use master
go
if object_id('sp_helptext2') is not null
drop proc sp_helptext2
go
create proc [dbo].sp_helptext2(@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