Search a column name across all databases

Question

I have this query that finds all tables and views that matches my column name of a certain database. I am using SQL SERVER 2008

SELECT table_name FROM information_schema.columns
WHERE column_name = 'product_name'

I want to extend the capability of my query to search across all databases and even look for Stored procedures whose having my searched column name.

Answer 1

This script will search your column in all tables across all databases.

Create table #yourcolumndetails(DBaseName varchar(100), TableSchema varchar(50), TableName varchar(100),ColumnName varchar(100), DataType varchar(100), CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''%yourcolumnname%'''

select * from #yourcolumndetails
Drop table #yourcolumndetails 

Answer 2

You can use this query for find field name that used in SP/View or Function

SELECT 
    OBJECT_NAME(object_id) as 'Procedure/Function/View Name',
    definition
    --,*
FROM sys.sql_modules
WHERE 
    ( definition LIKE '%' + '<Your_Field_Name>' + '%' )

Answer 3

Finding code across databases, the easiest way I've found is to sling a view with the below SQL in each DB then put in a master view that UNIONs them across the DBs. Bit of a pain if you've got loads of DBs or some you can't touch, but works well otherwise for me.

select
    s.name as SchemaName,
    objs.name as ObjectName,
    objs.ObjectType,
    OBJECT_DEFINITION(objs.object_id) AS ObjectDefinition
from
    (SELECT object_id, name, 'Proc' as ObjectType
    FROM    sys.procedures p
        UNION
    select  object_id, name, 'View'
    from    sys.views 
        UNION
    SELECT  object_id, Name, type
    FROM    sys.objects o
    WHERE   o.[type] IN ('fn', 'fs', 'ft', 'if', 'tf')) objs
    inner join sys.objects o
        on objs.object_id=o.object_id
    inner join sys.schemas s
        on o.schema_id=s.schema_id

Columns (as in find a column in an abstract table on any database on the server), you could do the same thing and just UNION the information schemas, but that won't work for what I want so I'm on the hunt for a more general solution - will add it if I find it!