Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Which Stored Procedure changed Expand / Collapse
Author
Message
Posted Friday, March 14, 2008 8:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi all,
Are there any system stored procedures or anyway to know which user stored procedures changed in the past month? Your help is alway appreciated. I find this forum very helpful and life saving, not to mention time saving.
Post #469420
Posted Friday, March 14, 2008 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 5,233, Visits: 9,475
Yes, something like this:

SELECT SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS ProcName, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND LAST_ALTERED > GETDATE() - 7

John
Post #469424
Posted Friday, March 14, 2008 8:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
John's solution is nice, because it is using the INFORMATION_SCHEMA views (part of the SQL Standard :), and is portable). But if you prefer SQL Server 2005 (and 2008) system views, you can write the above like:
SELECT  schema_name(schema_id) + '.' + name AS ProcName
, modify_date
FROM sys.procedures
WHERE modify_date > GETDATE() - 7

Regards,
Andras




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #469436
Posted Friday, March 14, 2008 11:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thank you John and Andras. When I run the query it shows only newly created Procedures and not altered procedures that were already existing, even when the cirteria includes "LAST_ALTERED".
Post #469548
Posted Friday, March 14, 2008 12:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,278, Visits: 3,054
Are the stored procedures being dropped/created and not altered. I think this would render the previous solution invalid.



My blog: http://jahaines.blogspot.com
Post #469591
Posted Friday, March 14, 2008 12:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,278, Visits: 3,054
Ideally, you need to implement some sort of object tracking system. A few available options are source control and DDL triggers.

Source control allows you answer the big questions like, what changed, who changed it, and when was it changed. SSMS allows a snap in to some of the bigger source control systems. Go to the menu item Tool --> options --> source control.

DDL triggers give you the ability to track infomation about who drops/alter/creates objects, such as stored procedures. The great thing about this is you can even rollback the transaction and send a message saying dropping stored procedures is not allow.




My blog: http://jahaines.blogspot.com
Post #469599
Posted Tuesday, April 27, 2010 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 9:17 AM
Points: 40, Visits: 75
I'm not sure which server version you are using, but in SQL Server 2008 ( including Express ) you can sort the procedures in descending modified date in the Object explorer details view.

Open the stored procedures folder in the Object Explorer Details.
If the Modified Date is not displayed, right click on the 'Name' heading to view the shortcut menu. Check the 'Date Last Modified' option to display it.
Click on the field headings to sort the list of stored procedures.

Cheers
Chieko



Post #911207
Posted Wednesday, April 28, 2010 12:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:25 PM
Points: 60, Visits: 248
Regarding the statement that you could see changes in the dates when you used CREATE, but not when you use ALTER, with the queries provided in this tread...

What version of SQL Server are you using? I don't have a place to confirm anymore, but I seem to remember that in SQL 2000 (and maybe 2005), the DateModified value didn't get changed in the system tables when a stored procedure was ALTERed.

Of course, my memory could be way off, too. But it may be worth a quick test.



Post #912268
Posted Monday, May 3, 2010 7:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
This post was created in March of 2009 and now all of a sudden i am seeing responses. I could not follow ckuroda's instruction on how to see the dates on altered SPs. I am using version 2005.
Post #914676
Posted Monday, May 3, 2010 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
I'm surprised noone mentioned the Default Trace yet, that contains all the recent DDL Changes;
there's a built in report from the default trace in SSMS: shows table drops and created, proc and function drops/alters/creates and more:


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #914721
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse