How to Find a Table on a SQL Server across all Databases

As much as I hate searching for objects or not having complete information to troubleshoot an issue sometimes I have to go search for a table in the database.

To find which database contains a table I am looking for on a server I run the following script

-- SEARCH FOR OBJECT
-- FIND A TABLE IN ALL DATABASES ON SERVER.
EXEC sp_msforeachdb N'USE [?];
SELECT
@@SERVERNAME + '' | '' + SUSER_SNAME() + '' | ''+ DB_NAME() + '' | '' + CONVERT(VARCHAR(20),getdate(),120) as [SOURCE],
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ,
''[''+ CAST(@@SERVERNAME AS VARCHAR(100))+ ''].[''+ TABLE_CATALOG + ''].[''+ TABLE_SCHEMA + ''].[''+ TABLE_NAME + '']'' as [FQN]
FROM [?].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%mytableName%'';
'
GO

I combine this with registering a group of servers in management studio, and in one quick step I can search for a table in any set of servers and databases.

 

Advertisements

One Reply to “How to Find a Table on a SQL Server across all Databases”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s