Find the database in which a table exists in the current database server

1 --2 --find the database in which a table exists in the current database server, and the sqlserver2008 test passes3 --4 Declare @tableName varchar( -)5 --This sets the name of the table to be queried6 Set @tableName=' Products'7  8 --Clean up temporary tables9 if object_id('tempdb: #tmpdbs') is  not NULL BeginTen     Drop Table#tmpdbs One End A if object_id('tempdb: # #tmpResults') is  not NULL Begin -     Drop Table# #tmpResults - End the   - --Create a global temporary table manually, insert only using INSERT INTO, and cannot use select INTO, which automatically creates temporary tables - Create Table# #tmpResults ( -DbNamevarchar( -), +Namevarchar( -), -XTypevarchar( -) + ) A   at SelectName,row_number () Over(Order  byName) asrowID into#tmpdbs fromMaster.. sysdatabases Name - Declare @dbName varchar( -) - Declare @rowid int - Declare @count int -   - Set @rowid=1 in Select @count=Count(*) from#tmpdbs -   to  while @rowid <= @count + begin -     --print (@rowid) the     Select @dbName=[Name]  from#tmpdbswhererowID=@rowid *     exec('INSERT INTO # #tmpResults Select" "+@dbName+" "As Dbname,name,xtype from'+@dbName+'.. SysObjects Where (xtype="'U"'or xtype="'SN"') and Name=" "+@tableName+" "ORDER by Name') $     Set @rowid=@rowid+1Panax Notoginseng End -   the --View Results + Select *  from# #tmpResults A   the --Clean up temporary tables + if object_id('tempdb: #tmpdbs') is  not NULL Begin -     Drop Table#tmpdbs $ End $ if object_id('tempdb: # #tmpResults') is  not NULL Begin -     Drop Table# #tmpResults - End

Find the database in which a table exists in the current database server