Archive for March, 2014

How to search for the database name(s) and table name(s) in all databases on the SQL Server instance based on a column name?

Has anyone asked you for help in searching for the database name or table name in all the databases on the SQL Server instance with a column name provided? Sure, it sounds easy enough if you had one user database and it has 3 tables of which contains 5 columns each. If that’s the case, your help is probably not needed and you can go back to enjoying your Mocha Frappuccino. But, we live in a very interesting and practical world, where a SQL Server instance can contain multiple databases and each database contains multiple tables and each table can contain up to 20+ data columns. To look for the database name or table name in all the databases just based on a column name can be similar to finding a needle in a haystack:

Needle_In_A_Haystack

Using the following script to search for all the databases and tables that contains the given column name, your search time will be shortened and hopefully you can get back to enjoying your Mocha Frappuccino before it’s too late.

-- Search for the databases name and/or table name in all databases on the SQL Server instance
sp_MSForEachDB 'USE [?];

SELECT 
    Table_Catalog as DatabaseName
    ,Table_Schema as TableSchema
    ,Table_Name as TableName
    ,Column_Name as ColumnName
    ,Data_Type as DataType
    ,Character_Maximum_Length as MaxLength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_Name like ''%Products%''' -- replace the keyword Products with the column name keyword you wish to search 

This will be similar to having a big magnifying glass in the search for the needle in a haystack!

Needle_In_A_Haystack_Magnifying_Glass