Skip navigation

Retrieving a Database’s Stored Procedures and Change Dates

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.
SELECT specific_catalog, specific_schema, routine_type,
 routine_name, created,
ORDER 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.

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.