Search This Blog

Wednesday, July 23, 2008

Searching a Table in all databases on Current Server

You can use Information_Schema view Tables to see the table list in the current DB as:
Select * From DB1.Information_Schema.Tables

we will use the above Sql query with the following stored procedure to search a table.

Procedure: dbo.sp_MSforeachdb
Description: This 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.

so you can use it to find a table name in every database as:

Exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '

No comments: