CODEWITHSUNDEEP
sqlsql-server
Thunder
Thunder

Reputation: 10986

Find a database with a particular table OR Find a table in every database of SQL Server

I have a SQL Server with hundreds of databases and each database having hundreds of tables. Now I would like to find where in these databases is a table that I am looking for.

I could find if a table existed in individual database using 

use myDatabase 
select * from sys.tables  where name = 'mytable' 
GO

but using this means I have to manually change the database for hundreds of times . I would like to find the database name only. Is there a way out ?

Upvotes: 13

Views: 73228

Answers (7)

nzrytmn
nzrytmn

Reputation: 6911

this is also one of the way, similar with solution of @Jonathan :

exec sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%YourTableName%'''

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

This should do what you are looking for:

EXEC sp_MSforeachdb "use [?];select * from sys.tables where name='TableName' "

To include the name of the current database in the output use:

EXEC sp_MSforeachdb "use [?];select '[?]' as DatabaseName, * from sys.tables where name='TableName' "

Upvotes: 14

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Okay, if you're just wanting to find each database that contains a particular table, and aren't going to be querying the table, then you can just do:

create table #t (
    DBName sysname not null
)
go
exec sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.mytable'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t

(If you're not using multiple schemas in your databases, you won't need to specify dbo in the OBJECT_ID call, otherwise I use it to avoid finding tables in the wrong schema)

Upvotes: 16

Joe McDonald
Joe McDonald

Reputation: 31

I know this is an old thread but was high on my google search. So I wanted to contribute for others looking to find a database with a certain table in it. These apply to SQL Server 2008 - Current.

I started with this, which worked for my SA level login, but gave me issues with users that did not have permissions to all databases.

SELECT name
FROM sys.databases
WHERE CASE
WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name ) + '.[dbo].[mytablename]','U' )
END IS NOT NULL;

But ended up with this adding the HAS_DBACCESS(name) = 1 in restriction so that the query would not fail with a security error.

SELECT name
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1 and
CASE
WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name ) + '.[dbo].[mytablename]','U' )
END IS NOT NULL;

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 452947

SELECT DISTINCT  DB_NAME(database_id)
FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL) 
WHERE OBJECT_NAME(object_id,database_id) = 'mytable'

Upvotes: 7

Ben Thul
Ben Thul

Reputation: 32667

exec 'select ''?'', name from [?].sys.tables where name = ''yourTable'''

Upvotes: -1

Jonathan
Jonathan

Reputation: 12025

exec sp_msforeachdb @command1='     
USE ?;
select * from sys.tables where name = ''CLIENTS'''

Upvotes: 1

Related Questions