方法一,是我自己经常用到的:
Code
select a.name,b.text from sys.sysobjects a inner join syscomments b
on a.id = b.id and b.text like '%你要找的字符串%'
方法二:
刚在外文资料上看到的:
Code
--Declare whatever you can :)
Declare @SString nvarchar(50)
Declare @getdbname sysname
Declare @sqlstm nvarchar(1000)
Declare dbname cursor for
--get all the names of the Databases in order by name
Select '['+name+']' from master.dbo.sysdatabases order by name
open dbname
--Get the first Name
FETCH NEXT FROM dbname into @getdbname
WHILE @@FETCH_STATUS=0
BEGIN
--set the search string
SET @SString = '<Place the String Here>'
--append the search pattern
SET @SString = '%' + @SString + '%'
--set the statement to define the search condition, with variables
SET @sqlstm = '
Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name'',Routine_Definition
From '+ @getdbname+'.Information_Schema.Routines
Where PatIndex('+''''+@SString+''''+', Routine_Definition) > 0'
--Execute the Query
EXEC (@sqlstm)
FETCH NEXT FROM dbname into @getdbname
END
--Close the Cursor and Deallocate it from memory
Close dbname
Deallocate dbname
(出处:http://www.sqlservercentral.com/scripts/Search/63397/)
第二个用的cursor查找,所有库的,二者范围不一样。
供大家急需时参考。