SQL Server Performance Forum – Threads Archive

How to list stored procs ordered by date?

Hi, I would like to list all the Stored Procedures ordered by their updated date. The idea is to see quickly which SP has been recently updated. Thanks very much in advance for your help.

The query in itself is pretty easy: select name, crdate from sysobjects where xtype = ‘p’ order by crdate The problem is that there is no "update date" recorded by SQL Server: if you ALTER an SP, it keeps the original Create Date. You can DROP and CREATE your SPs, not forgetting to GRANT and DENY the appropriate permissions on each SP: this will re-establish the Create Date. Better still, keep this bit of information in two places:
(1) in a comment within the script of the stored procedure,
(2) in your documentation outside of the database.

Thanks Adriaan for the info. I was hoping that the date of the modified SP would be stored somewhere in SQL Server. Not the creattion date. Other dates outside of the DB are not unreliable enough. What I wanted to do was to review the SPs which were modified since last week. It seems pretty tedious to do versionning on SQL2K Stored procs. I hope the upcoming SQL Server 2005 will help to address this issue.

SQL Server does not store such information and the query used by Adrian is only way to get the information. The other option is to run PROFILER to know about more of granular information. In all SQL Server databases, both system and user defined, a table by the name of sysobjects stores the creation date among other data for all objects in the database. Unfortunately, the only value that is captured in any of system tables with respect to object dates is sysobjects.dbo.crdate, but not the last date/time modified that you are searching for. I haven’t seen such enhancement in SQL 2K5 beta until now, hoep for the best. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Is there a way to list all packages based on the last updated date? It seems that there’s no updateDate existed in the system tables in reference with dts packages. Is it correct?

]]>