查询某台服务器下所有的数据库中包含某个关键词的存储过程 或 视图

USE MASTER
GO
 
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tmpResult'))
begin
	drop table #tmpResult
end

create table #tmpResult
(
	DbName varchar(100),
	SpOrViewName varchar(100),
	[Type] varchar(2),
	Content varchar(100)
)
go
 
DECLARE @dbname VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE @searchKeyword VARCHAR(100)

	SET @searchKeyword='member_system_member_id'

DECLARE dbname_cursor SCROLL CURSOR FOR
Select Name FROM Master..SysDatabases order by Name 

OPEN dbname_cursor
FETCH  next FROM dbname_cursor INTO @dbname

WHILE @@fetch_status=0
BEGIN
	FETCH  next FROM dbname_cursor INTO @dbname
	set @sql='
USE [' + @dbname + ']'

	set @sql=@sql + '
insert into #tmpResult 
select ''' + @dbname + ''' as DbName,a.name as SpOrViewName,a.[type]
	,case when len(b.[definition]) > 100  then substring(b.[definition],1,100)
		else b.[definition]  end 
	as Content
from sys.all_objects a,sys.sql_modules b 
where a.is_ms_shipped=0 and a.object_id = b.object_id 
and a.[type] in (''P'',''V'',''AF'') 
and b.[definition] like ''%' + @searchKeyword + '%''' -- db fields,dbName etc
--order by a.[name] asc
	
	BEGIN try
		EXEC(@sql)
		--print @sql
	END TRY
	BEGIN CATCH
		IF(@@ERROR<>0)
		BEGIN
			PRINT ERROR_MESSAGE()
		END
	END catch
END

CLOSE dbname_cursor
DEALLOCATE dbname_cursor
GO

select * from tempdb..#tmpResult

drop table #tmpResult

执行结果