Finding an Object on a Server - SQL Server

{outline|||Step <1> - }

Background¶

Starting at a new job where we have a great number (50 to 100) databases, I frequently find myself in a situation where I know the name of a database object, but not what database it's in. The following code will help find what database(s) an object is in given the object's name.

Method¶

Setup a Linked Server¶

In my situation, I wanted to create a stored procedure on my local machine which would query all the objects on another server (our development environment). For your situation, you would replace "GenDev" in the following SQL statement with your linked server name, or remove "GenDev" altogether

Generating the SQL Statements¶

When executed, the following T-SQL code will generate a set of SQL statements which, when executed, will query all objects in all databases on the GenDev linked server. The collate SQL_Latin1_General_CP1_CI_AS clauses are necessary because not all databases necessarily have the same collation, and this helps avoid the associated collation-related error.

select 
    sql = case when name = (select min(name) from GenDev.master.dbo.sysdatabases) 
              then 'select ' else 'union select ' end
              + '[Database] = ''' + name + ''', [Object] = name collate '
              + 'SQL_Latin1_General_CP1_CI_AS, [Type] = type collate '
              + 'SQL_Latin1_General_CP1_CI_AS from GenDev.[' + name + '].dbo.'
              + 'sysobjects where name collate SQL_Latin1_General_CP1_CI_AS = '
              + '@name collate SQL_Latin1_General_CP1_CI_AS'

from 
    GenDev.master.dbo.sysdatabases 
order by 
    name

Creating the Stored Procedure¶

After running the above SQL statement, select all rows of the resultset and copy them to a new query window. Then before the SQL statements, write the header create procedure MyProcedureName(@name nvarchar(128)) as . After the SQL statements, write the line order by 1