获取数据库下所有的存储过程的定义
本文整理于2012-4
这个问题以前碰到过，方法二是SQL调优培训那天请教了培训老师，培训老师给了我这个思路，我根据老师的思路写出来的，看到遇到技术瓶颈时变通思路是很重要的。
如下:
我们在开发数据库的SP经常会碰到这样的情况，
1.系统上线后由于还有一部分功能不是很完善或是需求变更，需要经常更新存储过程。
有时候会从开发库上更新到一个生产库，或则更新到多个生产库中(各分公司数据库独立)
由于更新的存储过程有些多，而且到后来经常不知道哪些存储过程是 已经修改过的，哪些是新增加的。
这个情况在我原来的公司经常碰到，各分公司各办事处的某些数据库都是独立的，需经常变更SP。
2.由于数据表设计变更或其它变更，需要更新相关的SP，可是不知道哪些SP需要涉及更新，需要获取所有SP的定义后再查找特定字符串。在Avene工作中有碰到这种情况。
有以下2个方法获取所有SP定义：
方法一
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
这个方法的优点是快捷方便，可是当SP定义大于8000个字节时，会阶段SP的定义，不能完整获取超长的SP的定义。在我们Avene的数据库中有的SP定义已超过了2000行了。
方法2
在方法1的基础上加以改进折中
use avene_dev
go
--一数据准备
CREATE TABLE sp_def--这个表存储SP的定义，表命名尽量短小，因为要拼接字符串时候要用到
(
id INT IDENTITY(1,1),
definiton VARCHAR(max)
)
create table tableobject--这个表存储SP的命名
(
objectname varchar(1000),
)
go
CREATE TRIGGER tr ON tableobject--将SP命名插入该表时触发器生成组合字符串
AFTER INSERT
AS
DECLARE @sql VARCHAR(max)
SET @sql=''
SELECT @sql=@sql+'insert sp_def EXEC sp_helptext ''' + objectname+ '''; '
FROM Inserted;
SELECT @sql '组合完成的字符串'
---------
--二数据执行
--1拼接语句
TRUNCATE TABLE tableobject
INSERT tableobject SELECT OBJECT_SCHEMA_NAME(id)+'.'+name from sys.sysobjects where xtype='p'
--得到类似如下的语句,注意该语句不能超过个字节，SQLserver本身的限制
--insert into EXEC sp_helptext 'dbo.Possp_smsByCity'; insert into EXEC sp_helptext 'dbo.';
--2执行上句生成的字符串
TRUNCATE TABLE sp_def
--执行'拼接字符串'
--3利用游标输出sp_def表中的SP的定义
go
declare @proc_text varchar(max)
DECLARE get_proc_text_cursor CURSOR FOR
SELECT definiton
FROM sp_def ORDER BY id
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
总结:方法2只是在方法1上做了折中变通，还没有突破字符串变量长度为8000个字节的限制，不知道Sqlserver是否有支持超过8000个字节的文本变量。
本人只是抛砖引玉，如果哪位有更好的方法实现或者能突破文本变量为8000个字节的，望不吝赐教！
