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.

ITPro Today logo in a gray background | ITPro Today

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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like