sp_helptext输出格式错行问题

在开发环境中常遇到更改存储过程、函数、视图等对象,解决SQL Server2005里sp_helptext输出格式错行问题。

SQL code

use Master
go
if object_id('SP_SQL') is not null
drop proc SP_SQL
go
/******************************************************************************************************************************************************
整理人：中国风(Roy)
日期:2008.01.01
******************************************************************************************************************************************************/
create proc [dbo].[SP_SQL](@ObjectName sysname)
as
set nocount on ;
declare @Print varchar(max)
if exists(select 1 from syscomments  where ID=object_id(@ObjectName) and encrypted=1)
begin
Print N'對象已加密!'
return
end
if coalesce(object_id(@ObjectName,N'P'),object_id(@ObjectName,N'FN'),object_id(@ObjectName,N'IF'),object_id(@ObjectName,N'TF'),object_id(@ObjectName,N'TR'),object_id(@ObjectName,N'V')) is  null
begin
Print N'對象只針對函數、存儲過程、觸發器、視圖!'
return
end
print 'Use '+db_Name()
print 'Go'
print 'if object_ID('+quotename(case when charindex(']',@ObjectName)=0 then '['+replace(rtrim(@ObjectName),'.','].[')+']' else @ObjectName end ,'''')+') is not null'
print char(9)+'Drop '+case  when object_id(@ObjectName,N'P') is not null then 'Procedure ' when Coalesce(object_id(@ObjectName,N'FN'),object_id(@ObjectName,N'IF'),object_id(@ObjectName,N'TF')) is not null then 'Function ' when object_id(@ObjectName,N'TR') is not null then 'Trigger ' else 'View 'end +case when charindex(']',@ObjectName)=0 then '['+replace(rtrim(@ObjectName),'.','].[')+']' else @ObjectName end
Print 'Go'
declare @T table(Col nvarchar(max))
insert @T select object_definition(object_id(@ObjectName)) --+char(13)+char(10)  
while (select max(Col) from @T)>''
begin
select top 1 @Print=left(Col,charindex(char(13)+char(10),Col)-1) from @T
print @Print
update @T set Col=stuff(Col,1,charindex(char(13)+char(10),Col)+1,'')
end
print 'Go'
go
調用方法:
use test--指定所屬數據庫  
exec sp_sql 'sp_sql'--指定對象 

生成一周內更改、創建的對象(改存储过程、函数、视图、觸發器)腳本 


SQL code 

use test--指定數據庫
go
declare T_cursor cursor local for
select quotename(Name)
from sys.objects a
where type in('P','TR','FN','IF','TF','V') and  datediff(d,modify_date,getdate()-7)<=0
and not exists(select 1 from syscomments where ID=a.object_ID and encrypted=1)
order by create_date asc,modify_date asc
declare @sql nvarchar(1000)
open T_cursor
fetch next from T_cursor into @sql
while @@fetch_status=0
begin
exec sp_sql @sql
fetch next from T_cursor into @sql
end
close T_cursor
deallocate T_cursor