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

USE MASTERGO if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tmpResult'))begindrop table #tmpResultendcreate 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 FORSelect Name FROM Master..SysDatabases order by Name OPEN dbname_cursorFETCH  next FROM dbname_cursor INTO @dbnameWHILE @@fetch_status=0BEGINFETCH  next FROM dbname_cursor INTO @dbnameset @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 Contentfrom 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] ascBEGIN tryEXEC(@sql)--print @sqlEND TRYBEGIN CATCHIF(@@ERROR<>0)BEGINPRINT ERROR_MESSAGE()ENDEND catchENDCLOSE dbname_cursorDEALLOCATE dbname_cursorGOselect * from tempdb..#tmpResultdrop table #tmpResult

执行结果