SQL query to list databases that have a given table

Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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%’

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    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'' 
    '
    1,660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following