学步园

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

	if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tmpResultErrorMsg'))  
    begin  
        drop table #tmpResultErrorMsg  
    end  
      
    create table #tmpResult  
    (  
        [ObjectName] varchar(100), 
		[Type] varchar(10),
		[TypeDesc] varchar(100),
        [DbName] varchar(100)
    )  

	create table #tmpResultErrorMsg  
    (  
        [UserAccount] varchar(50), 
		[DbName] varchar(50),
		[ShortIssue] varchar(20),
        [ErrorMsg] varchar(500)
    )  

    go  
      
    DECLARE @dbname VARCHAR(100)  
    DECLARE @sql VARCHAR(4000)  
    DECLARE @searchKeyword VARCHAR(100)  
      
        SET @searchKeyword='GMPI'  

	DECLARE @sErrorMsg VARCHAR(500)
	DECLARE @sUserAccount VARCHAR(50)
	DECLARE @sShortIssue VARCHAR(20)
      
    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() 

				SET @sErrorMsg= ERROR_MESSAGE() 


				DECLARE @iStartPos int=charindex('The server principal "',@sErrorMsg)
				DECLARE @iEndPos int=charindex('" is not able to access',@sErrorMsg)

				if(@iStartPos>0 and @iEndPos>0) 
				begin
					SET @sUserAccount=SUBSTRING(@sErrorMsg,@iStartPos + len('The server principal "'),@iEndPos-@iStartPos-len('" is not able to access')+1) 
					SET @sShortIssue ='Permission Access'
				end
				else begin
					SET @sShortIssue ='Other'
				end
				
				--SET @iStartPos=charindex('the database "',@sErrorMsg)
				--SET @iEndPos=charindex('" under the current security context.',@sErrorMsg)
				--SET @sDbName=SUBSTRING(@sErrorMsg,@iStartPos,@iEndPos-@iStartPos) 

				--@dbname

				insert into #tmpResultErrorMsg 
				select @sUserAccount,@dbname,@sShortIssue,@sErrorMsg

            END  
        END catch  
    END  
      
    CLOSE dbname_cursor  
    DEALLOCATE dbname_cursor  
    GO  
      
    select * from tempdb..#tmpResult order by Type
    select * from tempdb..#tmpResultErrorMsg 

    drop table #tmpResult  
	drop table #tmpResultErrorMsg