Home > IT Answers > Development > SQL query to list databases that have a given table
alessandro.panzetta
9,660 pts.
 SQL query to list databases that have a given table
Database programming, SQL, SQL queries
Hi all I need your help for creating some SQL query that returns the names of all databases that have a given table name. Ex. I'd like to run this query against the master database and search for all databases that have a table called "customers". Is it possible to do this, if yes how? Thanks in advance

Software/Hardware used:
ASKED: March 20, 2009  5:38 AM
UPDATED: March 20, 2009  10:28 PM
RELATED QUESTIONS
Answer Wiki:
SELECT name FROM master..sysdatabases lists all databases on your server. So you could just make a cursor on all databases and query if your table belongs to those. But ofcourse that implies your should have the neccessary rights to interrogate all databases. DECLARE @name VARCHAR(50) -- database name DECLARE db_cursor CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN exec ('use ' +@name+' SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where table_name=''customers'' if @@rowcount<>0 select '''+@name+''' as DataBaseName') FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor ---------------------- from one query knowing all tables in all databases, i can't be sure but i don't think it exists but you may run on any databse the below query, and you will know if such as this table exists in the chosen datase or doesn't SELECT * FROM sys.Tables where name like 'customers%'
Last Wiki Answer Submitted:  March 20, 2009  11:29 am  by  Sous   580 pts.
All Answer Wiki Contributors:  Sous   580 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

You can use undocumented SP_MSFOREACHDB stored proc. :

sp_msforeachdb @command1='use[?];
SELECT table_catalog as DB, table_name
FROM information_schema.Tables
where table_name = ''customers'' 
'
msi77
 1,610 pts.