查询存储过程里面的含有的关键字

由于在公司服务器上在某个时段查询某个sql执行比较慢，然后查到了某个sql里面含有in，造成数据库服务器经常超时，又不想一条一条的看，然后写了一个方法

 

CREATE Procedure GetKey @Key Varchar(200)='',@Type VarChar(10)='and'
As
Set NoCount On
Declare @S Varchar(400),@If Varchar(400)
Set @S='Select ''SPText '' as SH,Object_Name(S.[Object_ID]) as Obj,O.Modify_Date'+Char(13)
Set @S=@S+' From Sys.SQL_Modules S Join Sys.Objects O On S.Object_ID=O.Object_ID'+Char(13)
Set @If=''
While CharIndex('..',@Key)>0
Begin
Set @If=@If+' '+@Type+' S.[Definition] Like ''%'+SubString(@Key,1,CharIndex('..',@Key)-1)+'%'''+Char(13)
Set @Key=SubString(@Key,CharIndex('..',@Key)+2,80)
End
If @Key>'' Set @If=@If+' '+@Type+' S.[Definition] Like ''%'+@Key+'%'''+Char(13)
If @If>'' Set @If=' Where '+SubString(@If,Case When @Type='and' Then 5 Else 4 End,400)
Exec(@S+@If+' Order By 3 Desc') 

转载于:https://www.cnblogs.com/zhian/p/6073273.html