學海無邊，書囊無底

Use AdventureWorks
Declare @Text as varchar(100),@ObjectType as varchar(100)
Set @ObjectType = 'usp%' --資料庫物件名稱(Stored Procedure、View、…的名稱)，如uspGetEmployeeManagers
Set @Text = '%Employee %'   --"關鍵字"：資料庫物件的內容

Select s.Name SchemaName,b.name as TableName , d.text
From sysobjects b
Left Join syscomments d On d.id = b.id
JOIN sys.schemas s ON schema_id=uid
Where b.name like @ObjectType
and  d.text like @Text 
order by tablename

__________________________________________________________________________________

你也可以將其改為預存程序，程式碼如下：
CREATE PROC spFindObject
(@Text as varchar(100),@ObjectType as varchar(100))
AS
DECLARE @db SYSNAME

CREATE TABLE #tb(DBName SYSNAME,SchemaName SYSNAME,TableName SYSNAME,[Text] NVARCHAR(MAX))

DECLARE cur CURSOR FAST_FORWARD
FOR SELECT Name FROM master.sys.databases WHERE database_id>4  -- 只找使用者 DB

OPEN cur

FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
INSERT #tb EXEC(
'Select ''' + @db +'''
DBName, s.Name SchemaName,b.name as TableName , d.text
From ' + @db +'.sys.sysobjects b
Left Join ' + @db +'.sys.syscomments d On d.id = b.id
JOIN ' + @db +'.sys.schemas s ON schema_id=uid
Where b.name like ''' + @ObjectType + '''
and  d.text like ''' + @Text +''' 
order by tablename'
)
FETCH NEXT FROM cur INTO @db
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM #tb
go
exec spFindObject '%Emp%','%Emp%'