SQL Server provides mechanisms to determine the creation and modification dates of database objects like stored procedures, functions, views, and triggers. This information is valuable for auditing, change management, and debugging. This tutorial explains how to retrieve these dates using system views and information schema views.
Understanding Object Metadata
SQL Server stores metadata about database objects in various system views and catalog views. These views provide access to information like object names, types, creation dates, modification dates, and more. The sys.objects
catalog view is a central repository for this metadata. However, for more standardized access across different database platforms, the INFORMATION_SCHEMA
views can also be used.
Retrieving Creation and Modification Dates using sys.objects
The sys.objects
view contains comprehensive information about all objects within a SQL Server database. To retrieve the creation and modification dates, you can use the following query:
SELECT
name,
create_date,
modify_date
FROM
sys.objects
WHERE
type IN ('P', 'FN', 'V', 'TR') -- Filter for Procedure, Function, View, Trigger
ORDER BY
modify_date DESC;
name
: The name of the database object.create_date
: The date and time the object was created.modify_date
: The date and time the object was last modified.type
: Indicates the type of object. Common values include:P
: Stored ProcedureFN
: Scalar FunctionV
: ViewTR
: SQL Trigger
ORDER BY modify_date DESC
: Orders the results with the most recently modified objects appearing first.
Filtering by Object Type
To retrieve information for a specific type of object, you can refine the WHERE
clause. For example, to find the modification dates of only stored procedures:
SELECT
name,
create_date,
modify_date
FROM
sys.objects
WHERE
type = 'P'
ORDER BY
modify_date DESC;
Similarly, to retrieve information for functions, use type = 'FN'
.
Using INFORMATION_SCHEMA.ROUTINES
The INFORMATION_SCHEMA.ROUTINES
view provides a standardized way to access routine metadata (procedures and functions) across different database systems. This view can be particularly useful when writing cross-platform applications.
Here’s how to retrieve the creation and modification dates of routines using INFORMATION_SCHEMA.ROUTINES
:
SELECT
ROUTINE_NAME,
CREATED,
LAST_ALTERED
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION')
ORDER BY
LAST_ALTERED DESC;
ROUTINE_NAME
: The name of the stored procedure or function.CREATED
: The date and time the routine was created.LAST_ALTERED
: The date and time the routine was last modified.ROUTINE_TYPE
: Indicates whether the object is a ‘PROCEDURE’ or ‘FUNCTION’.
Filtering by Schema and Routine Name
You can also filter results by schema and routine name. For example, to retrieve the modification date for a specific procedure named ‘myProc’ in the ‘dbo’ schema:
SELECT
ROUTINE_NAME,
CREATED,
LAST_ALTERED
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME = 'myProc';
Important Considerations
- The
modify_date
(insys.objects
) orLAST_ALTERED
(inINFORMATION_SCHEMA.ROUTINES
) reflects the last time the definition of the object was altered. It doesn’t track data modifications within tables accessed by those objects. - Permissions: You need the appropriate permissions to query these system views and schema views. Typically,
VIEW DEFINITION
permission is sufficient. - Performance: While these queries are generally efficient, querying large numbers of objects can impact performance. Consider adding appropriate filters to narrow down the results.