用游标遍历某台服务器下所有的数据库中某个字段所在的表名及数据库名

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
(
	TableName 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(1000)
DECLARE @searchFieldname VARCHAR(100)

	SET @searchFieldname='Mbr_Elig_Term_Date'

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 TABLE_NAME as TableName,'''+ @dbname + 
		''' as DbName FROM  INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=''' + @searchFieldname +  ''' order BY TABLE_NAME'
	 
	BEGIN try
		EXEC(@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  
	                 
	                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 TableName  
select * from tempdb..#tmpResultErrorMsg   

drop table #tmpResult    
drop table #tmpResultErrorMsg