June 25, 2013 at 9:38 pm
Hello,
I can get a list of all the stored procedures modified since a given date -- but now I want to script all of those procedures (so I can port changes over to another server) ...
Does such functionality exist in SQL Server Management ??
Thanks in advance.
June 25, 2013 at 10:07 pm
Procedures and their definitions modified in the last 7 days
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.modify_date >= getdate()-7
AND o.type = 'P'
ORDER BY o.modify_date DESC;
GO
June 25, 2013 at 10:21 pm
Andrew G,
Brilliant -- Thanks!
I also found the following, which could be useful once you have a list of all the procedure names:
/*
* Returns the source text for the stored procedure ...
* From: http://msdn.microsoft.com/en-us/library/ms176090(v=sql.105).aspx
*/
SELECT OBJECT_DEFINITION( OBJECT_ID( N'sp_name' ) );
OR ...
/*
* Returns the source text for the stored procedure ...
* From: http://msdn.microsoft.com/en-us/library/ms176112(v=sql.105).aspx
*/
EXEC sp_helptext 'dbo.sp_name';
Cheers!
June 25, 2013 at 10:23 pm
There are several database compare tools out there. One that I've always like is SQLCompare by Red Gate.
In SQL 2000 that wouldn't have always worked, because using ALTER didn't update the date correctly, but in SQL 2005 I believe that problem is fixed.
I use a SQL compare tool myself though, so I can't vouch for that method 100%
June 26, 2013 at 1:02 am
Andrew G (6/25/2013)
Procedures and their definitions modified in the last 7 days
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definitionFROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.modify_date >= getdate()-7
AND o.type = 'P'
ORDER BY o.modify_date DESC;
GO
thanks Andrew, learn new thing 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply