如何查找自己写的sql server 哪些存储过程,函数、触发器包含某个指定的字符串 ?
1.查找 过程、函数 代码里是否包含指定的串:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%搜索的字符串%'
-------------------------------------------------------------------------------------------------------------
2.查找 触发器 代码里是否包含指定的串:
set nocount on
Create table #y (Trname varchar(50),txt text)
select name, iid = identity(int,1,1) into #x from SysObjects where xtype = 'TR'
declare @i int, @max int
declare @name varchar(50)
set @i = 1
select @max = max(iid) from #x
while @i <= @max
begin
select @name = name from #x where iid = @i
insert #y (txt)
exec('sp_helptext ' + @name)
update #y
set Trname=@name
where Trname is null
set @i = @i + 1
end
select * from #y where txt LIKE '%搜索的字符串%'
drop table #x
drop table #y
set nocount off