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 ?
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)
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' "
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'
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;
exec sp_msforeachdb @command1='
USE ?;
select * from sys.tables where name = ''CLIENTS'''
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%'''
exec 'select ''?'', name from [?].sys.tables where name = ''yourTable'''
