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 a Column on a Server - SQL Server

RSS
Modified on Tue, Feb 17, 2009, 2:14 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 column that I know is in either a table or a view, but not what database it's in. The following code will help find what database(s) an column is in given the column'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.

use master
select 
    sql = case when name = (select min(name) from GenDev.master.dbo.sysdatabases) 
			then '' else 'union ' end + 'select [Database] = ''' + name + ''', [Object] = o.name '
        + 'collate SQL_Latin1_General_CP1_CI_AS, [Type] = o.type collate '
        + 'SQL_Latin1_General_CP1_CI_AS, [Column] = c.name collate '
        + 'SQL_Latin1_General_CP1_CI_AS from GenDev.[' + name + '].dbo.sysobjects o inner join '
        + 'GenDev.[' + name + '].dbo.syscolumns c on o.id = c.id where o.type in (''U'', ''V'') '
        + 'and c.name collate SQL_Latin1_General_CP1_CI_AS = @name collate '
        + 'SQL_Latin1_General_CP1_CI_AS'
from
    GenDev.master.dbo.sysdatabases
where 1=1
    and name not in ('master', 'tempdb', 'model', 'msdb')

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, 2

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