Generate Multiple and Filtered Drop + Create Stored Procedures

Question

I have this script:

select name,create_date,modify_date from sys.procedures order by modify_date desc

I can see what procedures were modified lately. I will add a "where modify_date >= " And I'd like to use some system stored procedure, that will generate me : drop + create scripts for the (let's say 5 matching) stored procedures

Can i do this somehow?

thanks

Answer 1

Hi,

You could use a cursor to iterate through each record:

DECLARE @spName NVARCHAR(128)
DECLARE myCursor CURSOR FOR SELECT name FROM sys.procedures ORDER BY modify_date DESC
OPEN myCursor
FETCH NEXT FROM myCursor INTO @spName
WHILE @@fetch_status = 0
BEGIN
    -- Process each stored procedure with a dynamic query
    PRINT @spName
FETCH NEXT FROM myCursor INTO @spName
END
CLOSE myCursor
DEALLOCATE myCursor

Answer 2

This ain't pretty, but it works. Run the output from it manually or execute it with sp_executesql.

SELECT OBJECT_DEFINITION(object_id), 'drop procedure [' + name + ']'
FROM   sys.procedures
WHERE modify_date >= @date

You will have to worry about lost rights as well.

Answer 3

meanwhile i did some digging, and seems like

sp_helptext 'my_stored_procedure'

is what i need, (plus the part that i already knew when i asked the question explained more by jdecuyper)

Answer 4

No cursor necessary (modify as desired for schemas, etc):

DECLARE @dt AS datetime
SET @dt = '10/1/2008'

DECLARE @sql AS varchar(max)

SELECT @sql = COALESCE(@sql, '')
    + '-- ' + o.name + CHAR(13) + CHAR(10)
    + 'DROP PROCEDURE ' + o.name + CHAR(13) + CHAR(10)
    + 'GO' + CHAR(13) + CHAR(10)
    + m.definition + CHAR(13) + CHAR(10)
    + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
INNER JOIN sys.procedures AS p
    ON m.object_id = p.object_id
WHERE p.modify_date >= @dt

PRINT @sql -- or EXEC (@sql)

Answer 5

This is best done in a more suitable language than SQL. Despite its numerous extensions such as T-SQL, PL/SQL, and PL/pgSQL, SQL is not the best thing for this task.

Here is a link to a similar question, and my answer, which was to use SQL-DMO or SMO, depending on whether you have SQL 2000 or 2005.

How to copy a database with c#

Answer 6

ok. i have the final version:

http://swooshcode.blogspot.com/2008/10/generate-stored-procedures-scripts-for.html

you guys helped a lot

thanks