怎样获取某个数据库的所有存储过程SQL语句

Question

如题所述

Answer 1

　　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) + 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