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 <mydatabase>  -- The database that holds
  -- the stored procedures and functions.
GO
SELECT specific_catalog, specific_schema, routine_type,
 routine_name, created,
FROM INFORMATION_SCHEMA.ROUTINES
ORDER BY 1 asc, 2 asc, 3 asc, 4 asc</mydatabase>

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.