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

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window

sp_msforeachdb @command1=’use[?];
SELECT table_catalog as DB, table_name
FROM information_schema.Tables
where table_name = ”customers”
‘