Querying Stored Procedures Created or Modified on a Specific Date in SQL Server

Introduction

In database management and maintenance, tracking the creation and modification history of stored procedures is a common task. For instance, developers may need to find stored procedures created or modified on a specific date, such as September 27, 2012, for version control or auditing purposes. SQL Server offers multiple system views to access this metadata, but different methods vary in accuracy and compatibility. This article delves into two primary query approaches, providing detailed guidance based on best practices.

Using the sys.procedures System View

sys.procedures is a system view in SQL Server used to query information about stored procedures, returning metadata for all stored procedures in the current database. This view includes several fields, where name represents the stored procedure's name, create_date records the creation date and time, and modify_date records the last modification date and time. These date values are stored as datetime data types, allowing precise time-based filtering.

To query stored procedures created on a specific date, use the following SQL statement:

SELECT 
    name,
    create_date,
    modify_date
FROM sys.procedures
WHERE create_date = '20120927'

In this query, the WHERE clause uses create_date = '20120927' to filter stored procedures with a creation date of September 27, 2012. The date string '20120927' employs the YYYYMMDD format, a standard representation for datetime data types in SQL Server, avoiding ambiguity from regional settings. The result will list the names, creation dates, and modification dates of matching stored procedures.

However, this method has limitations. First, sys.procedures only records the creation and modification times of stored procedures, not the users who performed these actions. This means it cannot directly determine who created or modified a stored procedure, which may be insufficient for certain auditing scenarios. Second, if a stored procedure was modified but not created on the specified date, the above query will not return those records, as it only filters by create_date. To query both created and modified stored procedures, extend the WHERE clause, for example:

SELECT 
    name,
    create_date,
    modify_date
FROM sys.procedures
WHERE create_date = '20120927' OR modify_date = '20120927'

This query uses the OR operator to match stored procedures where either the creation date or modification date is the specified date. Note that the modify_date field updates when a stored procedure is modified, but some operations (e.g., renaming) might not trigger updates, so caution is advised in practical applications.

Using the INFORMATION_SCHEMA.ROUTINES View

As a supplementary method, the INFORMATION_SCHEMA.ROUTINES view offers another way to query stored procedure information. This view adheres to SQL standards and is often used for cross-database system compatibility. It includes fields such as ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE, and CREATED, where CREATED records the creation date.

Here is an example query:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' 
and CREATED = '20120927'

In this query, the WHERE clause first filters for stored procedures with ROUTINE_TYPE = N'PROCEDURE', then specifies the schema (default is dbo) with ROUTINE_SCHEMA = N'dbo', and finally matches the creation date with CREATED = '20120927'. Compared to sys.procedures, INFORMATION_SCHEMA.ROUTINES has more standardized field names but may have incomplete or delayed information in some SQL Server versions. For instance, it might lack a modification date field, making it unsuitable for querying modification records.

Practical Considerations

When applying these queries in practice, several key points must be considered. First, date formats must be correct to avoid errors. SQL Server's datetime type supports various input formats, but YYYYMMDD is the unambiguous recommended format. If other formats (e.g., DD/MM/YYYY) are used, conversion functions or language settings may be necessary.

Second, permission issues can affect query results. Accessing sys.procedures and INFORMATION_SCHEMA.ROUTINES typically requires certain database permissions, such as VIEW DEFINITION permission. If user permissions are insufficient, queries may return empty results or errors.

Additionally, for large databases, these queries might involve full table scans, impacting performance. Optimization can be achieved by creating indexes on relevant fields, but maintenance costs should be weighed. In dynamic environments where stored procedure creation and modification occur frequently, regular auditing combined with version control tools is recommended.

Conclusion

Querying stored procedures created or modified on a specific date in SQL Server is a straightforward task that requires attention to detail. The sys.procedures view provides the most accurate and comprehensive information, suitable for most scenarios, while INFORMATION_SCHEMA.ROUTINES serves as a cross-platform compatible supplement. By properly using the WHERE clause and date formats, developers can efficiently track stored procedure changes, supporting database management and auditing needs. In the future, as SQL Server versions update, more metadata views or enhanced features may become available, so it is advisable to refer to official documentation for the latest information.