Select databases by a certain table
-
Hello!
I need to select all databases that contain a certain table. I've tried looping trough the databases found on the server and testing if that table exists, but at some of them an error is returned ( Msg 916, Level 14, State 1, Line 1 - user not valid).
Is there a simpler way to do that?
Thanks in advance,
Romulus C.
-
You could check the sysobjects table in each database - look for xtype = U
Get this: http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx
name sysname Object name. Id int Object identification number. xtype char(2) Object type. Can be one of these object types: C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
-
Thank you, that diagram saved me
Thanks again,
Romulus C.
-
To automate further you can use the sysDatabases table found in the master database to give you the list of all your databases.
-
You should use the Information_Schema views instead of the system tables whenever you can. The SQL 2000 system tables have been deprecated in SQL 2005 and will not be in the following version of SQL Server whereas MS will continue to keep the Information_Schema views backwards compatible throughout future version.
Exec
sp_MSForEachDB 'If Exists(Select 1 From ?.Information_Schema.Tables Where Table_Name = ''MyTableName'') Select ''?'''
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply