Listing databases that contain a specific table

Question

Listing databases that contain a specific table

kromo

SSC-Addicted

Points: 459
More actions
December 3, 2003 at 11:15 am

#162730

I am trying to get a list of databases that only contain a certain table. How can I do this? I have tried using :
Select Catalog_name from INFORMATION_SCHEMA.SCHEMATA
Where Exists(Select * from information_schema.tables where table_name = 'myTable')
but that does not work the way I want it to.
Please help, I have tried everything.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

try:

 



CREATE TABLE #T(Cat nvarchar(128))



INSERT INTO 

    #T

EXEC sp_MSForeachdb N'use ? 

SELECT  

     Table_Catalog 

FROM

     INFORMATION_SCHEMA.TABLES 

WHERE 

     table_name =''YOUR_TABLE_NAME'''



SELECT * from #T



DROP TABLE  #T

* Noel

kromo SSC-Addicted Points: 459 More actions · Answer 2

This works great but do you think you can explain:

EXEC sp_MSForeachdb N'use ?

Thanks a bunch!

noeld SSC Guru Points: 96590 More actions · Answer 3

sorry for the Delay (I was busy )

1.

sp_MSforeach... procedures are undocummented in BOL but you can always check their code in the master db!!

2 the '?' is changed automatically to the dbname in the case of foreachdb and to tablename in the case of foreachtable

now you can see that the end result is to execute the query in the context of each DB

hope is clear

* Noel

kromo SSC-Addicted Points: 459 More actions · Answer 4

I would be using this in a stored procedure that would be called from an application, is this a safe way to filter the databases?

Thanks in advance.

noeld SSC Guru Points: 96590 More actions · Answer 5

Can you define SAVE?

will it work? == Sure!

will it be supported in the next version ? == probably but Is not warratied

will it be a good idea for an application? == it is very dependent of the enviroment!! as you can see you are sweeping all databases that means for example that you need to take into account crossdatabase ownership chain, etc

I hope that my comments above can shed some light in your path

HTH

* Noel