Sunday, March 16, 2014

SQL Server - Know which Object exists in which Database

Sometimes, users need some features in SQL Server. Among them, one feature is to know the name of database in which the given object exists. A troubling situation is finding the name of database in which the given object exists when there are a lot of databases present in the server. For this, Microsoft provided a system Stored Procedure but it didn't provide an official documentation explaining it.

sp_MSforeachdb is an undocumented System Stored Procedure which makes our work easy in finding the database. Execute the following script in your Query Editor to find which object exists in which database. The object can be a Table, Function or a Stored Procedure.

EXEC sp_MSforeachdb
'if exists(select 1 from [?].sys.objects where name=''<Name of Object>​'')
select ''?'' as ''Database Name'' from [?].sys.objects where name=''<Name of Object>'''

No comments:

Post a Comment