在开发环境中常遇到更改存储过程、函数、视图等对象,解决SQL Server2005里sp

来源:http://topic.csdn.net/u/20080804/12/f477b3d8-0017-4ef9-b516-021032be83ce.html

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 ' -- 指定對象