Thursday, October 9, 2008

Generate Stored procedures scripts for SP-s modified after a specific date

When I am installing my changes in an other environment (test envir in my case) i need to get scripts for the modified stored procedures only. When you have a lot of sp-s, and you only need scripts for 3-4 of them, then you should use a script like this one:





CREATE PROCEDURE [dbo].[GenerateLatestProcedures]
@DateFrom DATETIME

AS
BEGIN

DECLARE @spName NVARCHAR(128), @object_id INT
DECLARE myCursor CURSOR FOR
SELECT name
FROM sys.procedures
WHERE modify_date >= @DateFrom OR create_date >= @DateFrom
ORDER BY modify_date DESC

OPEN myCursor
FETCH NEXT FROM myCursor INTO @spName
WHILE @@fetch_status = 0
BEGIN

PRINT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + CONVERT(VARCHAR(255), @spName) + ']'') AND type in (N''P'', N''PC''))
DROP PROCEDURE [' + CONVERT(VARCHAR(255), @spName) + ']'

SELECT @object_id = object_id FROM sys.procedures WHERE NAME = @spName
PRINT OBJECT_DEFINITION(@object_id)

FETCH NEXT FROM myCursor INTO @spName
END
CLOSE myCursor
DEALLOCATE myCursor

END