SQL Server - find Modified date of SP's - Asked By Harbans Singh on 19-Dec-08 12:54 AM

Dear All,

Please suggest how to find last modified date of SP's in MS SQL 2000 Server. Like if one SP has been made on 1/Dec/2008 and he has modified on 10/Dec/2008, and later it has modified on 13/Dec/2008.

So, Please suggest how can we see that this SP has been created on 10/Dec/2008 and last it has been modified on 13/Dec/2008.

Regards,

Sidhu

find Modified date of SP's - mv ark replied to Harbans Singh on 19-Dec-08 01:35 AM

As you are using SQL Server 2000, you can get only the created date, using this query -
select crdate from sysobjects where type='p' and  name = 'your_procedure_name'

If you maintain your stored procedures with a version control tool like VSS, you can better manage them.

re - Web Star replied to Harbans Singh on 19-Dec-08 08:02 AM

no way , u cant get modifed date directly u need to use data version control for that purpose

In all SQL Server databases, both system and user defined, a table by the name of http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-o_4zll.asp 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. This date would be very beneficial to validate a new stored procedure has been released, but the sysobjects.dbo.crdate value is not updated. One way to have SQL Server reflect the updates is to not http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_72sl.asp any stored procedures, which occurs by default when using Enterprise Manager, but rather script out the stored procedures and execute the code in Query Analyzer with a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_2j8l.asp and a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp statement each time an object is released. This way the value in sysobjects.dbo.crdate will reflect the date/time stamp for the latest changes.

SQL SERVER - 2005 - List All Stored Procedure Modified in Last N Days

C_A P replied to Harbans Singh on 20-Dec-08 01:22 AM

SQL SERVER - 2005 - List All Stored Procedure Modified in Last N Days

SELECT name
    FROM sys.objects
    WHERE type 'P'
        AND DATEDIFF(D,modify_dateGETDATE()) < 7
    ----Change 7 to any other day value

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
    FROM sys.objects
    WHERE type 'P'
        AND DATEDIFF(D,create_dateGETDATE()) < 7
    ----Change 7 to any other day value.

Date condition in above script can be adjusted to retrieve required data.

source from:

http://blog.sqlauthority.com/2007/08/10/sql-server-2005-find-stored-procedure-create-date-and-modified-date/

SQL Server Stored Proc last modified date - C_A P replied to Harbans Singh on 20-Dec-08 01:31 AM

You can't find it on SQL Server 2000 unless you have a version control.
In  sql 2005, you can check the field 'Modify_date' in the view 'sys.Procedures'

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. This date would be very beneficial to validate a new stored procedure has been released, but the sysobjects.dbo.crdate value is not updated. One way to have SQL Server reflect the updates is to not ALTER (PROCEDURE) any stored procedures, which occurs by default when using Enterprise Manager, but rather script out the stored procedures and execute the code in Query Analyzer with a DROP PROCEDURE and a CREATE PROCEDURE statement each time an object is released. This way the value in sysobjects.dbo.crdate will reflect the date/time stamp for the latest changes.

read this - C_A P replied to Harbans Singh on 20-Dec-08 01:33 AM
Is there a way to find out when a stored procedure was last updated?

Simple answer is 'No'. The crdate column in the sysobjects table always contains the stored procedure create date, not the last updated date. You can use Profiler to trace ALTER PROC calls to the database, but you can't really afford to run a trace for ever, as it's resource intensive. Here is a simple idea! Whenever you have to alter your stored procedure, first drop it, then recreate it with the updated code. This resets the crdate column of sysobjects table. If you can make sure your developers always follow this plan, then the crdate column of sysobjects will always reflect the last updated date of the stored procedure. For example, if I have to modify a procedure named MyProc, instead of doing "ALTER PROC MyProc", here's what I would do:

- Use sp_helptext to get the current code of MyProc.
- Change the code as needed.
- Run the following code to drop the existing version of MyProc:

IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'MyProc' AND type = 'P' AND USER_NAME(uid) = 'dbo')
BEGIN
DROP PROC dbo.MyProc
END


- Run the updated code to recreate MyProc

There is a much more powerful way out, if you can use Visual Source Safe (VSS). VSS is a version control software, that lets you manage your code. With VSS in place, you will have to maintain all your object creation scripts as script files and check them into VSS. When you have to modify a particular stored procedure, check out that script from VSS, modify it, test it, create the stored procedure, and check the script back into VSS. VSS can show you when a script got modified, by who and a whole lot of other information.

Advantages of using VSS
- You can version control your software, as VSS maintains all your changes as different versions
- You can go back to a previous known good version of your stored procedure, if a developer makes a mistake
- Using the labelling feature, you can revert back to an entire set of scripts at a particular point in time
- You can control access to your source code by configuring permissions to your developers
- By maintaining backups of VSS database, you can secure all your code centrally, instead of worrying about individual script files

For more information on VSS, visit
http://msdn.microsoft.com/ssafe/