Finding a Column 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 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