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

转载于:https://www.cnblogs.com/sanle/archive/2008/08/21/1273068.html