Trying to find the existence of a table in all databases

[goldwhispers]

I want to find if a table exists in all my databases on one server, is it possible to do this with one query? someone help.

thanks

 

[Catadmin]

Are all the databases on the same SQL Server & Instance? If so, you could potentially do a Select with a Union All and using a three part naming convention (Database.Owner.Table), but I've never actually done it myself.

If not, you'll need to make sure your servers are all linked together (registered on the same SQL Group in EM doesn't count) and you'll need to use the four part naming convention (Server.Database.Owner.Table) to see if that works.

The last option is to write the query, open a connection in QA to each database, and run the same query each time. Which I'm sure is the option you want to avoid.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[mrdenny]

Use something like this.

exec sp_MSforeachdb 'if exists (select * from ?.dbo.sysobjects where name = ''YourTableName'') print ''Exists in ?'''

Replace YourTableName with the name of the table you are looking for.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[timscronin]

I have 2 scripts, one to get columns, one to get tables will work for sql 2000

select sysobjects.name as 'table name'
from sysobjects
where
sysobjects.type = 'u'
and
sysobjects.name <> 'dtproperties'
order by sysobjects.name



Select
'Table Name ' = B.name,
'Column name' = A.name,
'Column No' = colid,
'Type' = type_name (xusertype),
'Length' = convert (int, length),
'Allow Nulls'= case when isnullable = 0 then 'No' else 'Yes' end
From syscolumns A, sysobjects B
Where A.id=B.id and B.type ='U' and A. number=0 and B.Status > 0
Order by b.name, colid