存储过程批量生成

?

DECLARE @proc_text            VARCHAR  (MAX) 

DECLARE get_proc_text_cursor           CURSOR 

FOR

    SELECT 'if object_id(N''' + [name] + ''') is not null drop proc ' + [name] +

           CHAR(10) + CHAR(13) + '  GO ' + CHAR(10) + CHAR(13) + ltrim(definition) +

           CHAR(10) + CHAR(13) + ' GO'

    FROM   sys.sql_modules

           INNER JOIN sysobjects

                ON  sys.sql_modules.object_id = sysobjects.id

                AND TYPE = 'p'

 

  OPEN get_proc_text_cursor 

   FETCH NEXT FROM get_proc_text_cursor  INTO @proc_text  

WHILE (@@FETCH_STATUS = 0)

BEGIN

    PRINT @proc_text

    FETCH NEXT FROM get_proc_text_cursor INTO @proc_text

END CLOSE get_proc_text_cursor 

DEALLOCATE get_proc_text_cursor