Monday, June 23, 2014

Finding a Table in all databases on Server

dbo.sp_MSforeachdb procedure will execute the attached string as a command against every database on the server. Any question mark(?) , within the string will be replaced by every database name.
We can use the Information_Schema view Tables to see the tables list in the current DB:

Select * From DB1.Information_Schema.Tables

So by combining both SP and View we can search for a Table in all databases in Server

EXEC dbo.sp_MSforeachdb 'SELECT ''?'', * FROM [?].INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ''Items'' '

No comments:

Post a Comment