Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Finding an Object on a Server - SQL Server

RSS
Modified on Tue, Feb 17, 2009, 2:01 PM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2017, Patrick Jasinski.