How to search a table in all the Databases in Server

PandianS
Posted by PandianS under Sql Server category on | Points: 40 | Views : 1777
I have one table called 'Tb_Table1'. But I don't In which Database the table exists. But, I have 100 Databases in server. So, I want to search the table in all the 100 Databases.

Without any Loop. How ?
USE MASTER
Go

CREATE TABLE #Result
(
[Database Name] VARCHAR(100),
[Table Name] VARCHAR(100)
)

INSERT #Result
EXEC('SP_MSFOREACHDB ''SELECT ''''?'''',NAME FROM ?.SYS.OBJECTS WHERE TYPE=''''U''''
AND NAME =''''Tb_Table1''''''')

SELECT * FROM #Result

DROP TABLE #Result

Comments or Responses

Login to post response