根据表名查找所在的数据库名(用游标)

USE MASTER
GO

DECLARE @dbname VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE @searchTablename VARCHAR(100)

	SET @searchTablename='rx_claim_detail'

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 + ' Select Name as TableName,'''+ @dbname + 
		''' as DbName FROM  SysObjects Where XType=''U'' and name =''' + @searchTablename +  ''' orDER BY Name'
	
	BEGIN try
		EXEC(@sql)
	END TRY
	BEGIN CATCH
		IF(@@ERROR<>0)
		BEGIN
			PRINT ERROR_MESSAGE()
		END
	END catch
END

CLOSE dbname_cursor
DEALLOCATE dbname_cursor
GO

修改版：

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

create table #tmpResult
(
	TableName varchar(100),
	DbName varchar(100)
)
go

DECLARE @dbname VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE @searchTablename VARCHAR(100)

	SET @searchTablename='rx_claim_detail'

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 Name as TableName,'''+ @dbname + 
		''' as DbName FROM  SysObjects Where XType=''U'' and name =''' + @searchTablename +  ''' orDER BY Name'
	
	BEGIN try
		EXEC(@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

修改版2：

>>>>

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

DECLARE @dbname VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE @searchTablename VARCHAR(100)

	SET @searchTablename='GroupProvider'

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 Name as TableName,'''+ @dbname + 
		''' as DbName FROM  SysObjects Where XType=''U'' and name =''' + @searchTablename +  ''' orDER BY 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  
 
  
                --@dbname  
  
                insert into #tmpResultErrorMsg   
                select @sUserAccount,@dbname,@sShortIssue,@sErrorMsg  
		END
	END catch
END

CLOSE dbname_cursor
DEALLOCATE dbname_cursor
GO

select * from tempdb..#tmpResult
select * from tempdb..#tmpResultErrorMsg   

drop table #tmpResult
drop table #tmpResultErrorMsg   

<<<<

执行结果：