适用于sql2k和2005
/*fcuandy*/
DECLARE
@s
VARCHAR
(
4000
),
@n
INT
,
@i
INT
,
@s1
VARCHAR
(
100
)
SELECT IDENTITY ( INT ) id, text INTO ##
FROM syscomments
SELECT @n = @@ROWCOUNT , @i = 0
WHILE @i < @n
BEGIN
SELECT @i = @i + 1 , @s = ''
SELECT @s1 = REPLACE ( REPLACE ( RTRIM ( LTRIM ( STUFF ( STUFF ( text , CHARINDEX ( ' AS ' , text ), 40000 , '' ), 1 , CHARINDEX ( ' PROC ' , STUFF ( text , CHARINDEX ( ' AS ' , text ), 40000 , '' )) + 4 , '' ))), CHAR ( 10 ), '' ), CHAR ( 13 ), '' )
FROM ## WHERE ID = RTRIM ( @i )
-- SELECT @s1,ASCII(SUBSTRING(@s1,3,1))
-- SELECT LEN(REPLACE(REPLACE(@s1,CHAR(13),''),CHAR(10),''))
SELECT @s = ' SELECT text FROM tempdb.dbo.## WHERE ID= ' + RTRIM ( @i )
EXEC ( ' EXEC master..xp_cmdshell '' bcp " ' + @s + ' " queryout "e:ProcTXT/ ' + @s1 + ' .txt" -S"pcnameSQLEXPRESS" -c -U"sa" -P"xxxx" ''' )
END
DROP TABLE ##
SELECT IDENTITY ( INT ) id, text INTO ##
FROM syscomments
SELECT @n = @@ROWCOUNT , @i = 0
WHILE @i < @n
BEGIN
SELECT @i = @i + 1 , @s = ''
SELECT @s1 = REPLACE ( REPLACE ( RTRIM ( LTRIM ( STUFF ( STUFF ( text , CHARINDEX ( ' AS ' , text ), 40000 , '' ), 1 , CHARINDEX ( ' PROC ' , STUFF ( text , CHARINDEX ( ' AS ' , text ), 40000 , '' )) + 4 , '' ))), CHAR ( 10 ), '' ), CHAR ( 13 ), '' )
FROM ## WHERE ID = RTRIM ( @i )
-- SELECT @s1,ASCII(SUBSTRING(@s1,3,1))
-- SELECT LEN(REPLACE(REPLACE(@s1,CHAR(13),''),CHAR(10),''))
SELECT @s = ' SELECT text FROM tempdb.dbo.## WHERE ID= ' + RTRIM ( @i )
EXEC ( ' EXEC master..xp_cmdshell '' bcp " ' + @s + ' " queryout "e:ProcTXT/ ' + @s1 + ' .txt" -S"pcnameSQLEXPRESS" -c -U"sa" -P"xxxx" ''' )
END
DROP TABLE ##
因为我建了两个存储过程进行测试的,一个为p1,一个为p2,存储过程名都只有2字符,用len查看却是4,就用substring(3,1)查,再取asc码,得到13,
才知道是多了换行符加回车符. 即 char(13)和char(10), /r/n, 呵呵.
开始,没处理这个时, 查询分析器老提示我,帐号不对,害我查了好多地方.
用 exec(' exec ... 嵌套的原因是,
exec master..xp_cmdshell执行时,参数是为常量字串,不能为变量, 所以只能多加一层exec ,让它构造出供内层 exec master..使用的字串常量.
在2k和2005下均可使用.
2005默认的使用windows认证,我的语句中用的SQL认证, 那么, 需要你适当的修改语句,或更改SQL注册属性.
另外2005下默认的xp_cmdshell是禁用的,你可以外围应用适配器里设置启用它,也可以使用sp_configure启用它.
生成的存储过程放在了 e:/ProcTXT 目录下,名为 过程名.txt 可自行修改.
使用时要注意的就这些了.