Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



Finding a Column on a Server - SQL Server

Modified on Tue, Feb 17, 2009, 2:14 PM by Administrator Categorized as SQL Server
{outline|||Step <1> - }


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.


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
    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'
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 Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.