Retrieving a Database’s Stored Procedures and Change Dates
Microsoft’s SQL Server development team sends readers to a database’s list of stored procedures and offers a code snippet that returns that information.
June 25, 2002
In SQL Server 2000, how can I list a database's stored procedures and their change dates?
The data you're looking for is stored in SQL Server 2000's system tables. Because Microsoft discourages users from querying the system tables directly (the tables change from release to release), we recommend that you retrieve this information from the ANSI-standard information schema views for each database. The following statement uses an information schema view to return the data you want:
USE -- The database that holds -- the stored procedures and functions.GOSELECT specific_catalog, specific_schema, routine_type, routine_name, created,FROM INFORMATION_SCHEMA.ROUTINESORDER BY 1 asc, 2 asc, 3 asc, 4 asc
Note that SQL Server retains only the created date for stored procedures, so to capture the date of the latest version, you need to drop, then create the procedure instead of using ALTER to modify or update the procedure.
About the Author
You May Also Like