Link to home
Start Free TrialLog in
Avatar of Tom Knowlton
Tom KnowltonFlag for United States of America

asked on

Determine if table exists in databases

Is there a T-SQL way to determine if a particular table exists in one or more databases, not knowing before hand how many databases there are?
SOLUTION
Avatar of becraig
becraig
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tom Knowlton

ASKER

If I wanted to output "  <<name>> exists in <<database name>>" or " <<name>> does not exist in <<database name>>"

What would the entire working T-SQL statement look like, where <table name> is "Foo"

(T-SQL is not my strength, sorry)
The quick-and-dirty, albeit not the most efficient way, is this:


EXEC sp_MSforeachdb '
USE [?]
IF EXISTS(SELECT 1 FROM sys.tables WHERE name = ''<table_name>'')
    PRINT ''Db ? contains a table named "<table_name>"''
ELSE
    PRINT ''Db ? does not contain a table named "<table_name>"''

'
SELECT * FROM sys.databases

SELECT * FROM sys.tables


Is there some way to join these results so I can say what table comes from what database?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The alternative is to write your own proc that loops thru the dbs :-(.  I have done that, because for complex tasks it gains a lot of performance, but for most normal db tasks you should be able to use the MSforeachdb proc just fine.
Ok

Thanks for the follow-up!
Okay, I get it now.

Thanks!

If a better way comes along, please come back and post, Scott.