USE MASTER 
    GO 
       
    if exists(select * from tempdb..sysobjects where id=object_id(‘tempdb..#tmpResult‘)) 
    begin 
        drop table #tmpResult 
    end 
      
    create table #tmpResult 
    ( 
        [ObjectName] varchar(100), 
  [Type] varchar(10),
  [TypeDesc] varchar(100),
        [DbName] varchar(100)
    ) 
    go 
      
    DECLARE @dbname VARCHAR(100) 
    DECLARE @sql VARCHAR(4000) 
    DECLARE @searchKeyword VARCHAR(100) 
      
        SET @searchKeyword=‘GMPI‘ 
      
    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 OBJECT_NAME(sm.object_id) AS ObjectName, o.type as Type
 , o.type_desc as TypeDesc,‘‘‘+ @dbname + ‘‘‘ AS DbName
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE UPPER(sm.definition) LIKE ‘‘%‘ + UPPER(@searchKeyword) + ‘%‘‘
ORDER BY o.type‘
     
        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  

用游标遍历某台服务器下所有的数据库中 汲及到某个关键词的 所有存储过程及自定义函数,布布扣,bubuko.com

用游标遍历某台服务器下所有的数据库中 汲及到某个关键词的 所有存储过程及自定义函数