Finding a SQL Server object quickly

Finding a SQL Server object, such as an table or a stored procedure, would be so painful as you have to go through the databases one by one*. Object Browser could of some help, but still it is time-consuming.

So, let’s begin by assuming that we don’t know where the table ‘TestTable’  is.

Possible way could be use the SysObjects table and query it like  
       Select * from SysObjects where name like ‘TestTable’

But the SysObjects table is database specific. So if you have a database named ‘DB1’ that has the ‘TestTable’ and you run the query in ‘DB’2, it will not give the expected result.

So if the database name is unknown?, then use this query below.


Exec sp_MSforeachdb  ' Select * from ?.. SysObjects where name like ‘TestTable’  ’

Better one:  Exec sp_MSforeachdb  '
If exists(select * from ?.. SysObjects where name = ‘TestTable’)select * from ?.. SysObjects where name = TestTable’  '
     This will give you the required information and will remove that redundant data.

Note: the '
?' mark in the query is replaced with the database names, internally  in the loop.


This procedure sp_MSforeachdb is undocumented by Microsoft. So you won’t find the help regarding this in the SQL Server Help. This procedure will run for all those databases present.




* If you don’t know the database name
By [)ia6l0 iii   Popularity  (1152 Views)