Search a column name across all databases

sql-server-2008 information-schema
SyntaxError picture SyntaxError · Aug 21, 2012 · Viewed 13.8k times · Source

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

Shiva picture Shiva · Apr 11, 2014

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

Related questions

Find all tables whose name ends with a certain suffix

I have thousand of tables in database. Some names end with _History. For example : abc_History bcd_History 123_History How do I find all tables which name is end with _History. Some thing like: SELECT table_name FROM sys.tables …

Read More
SQL Server 2008 grant permission to information_schema.columns

I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called recControl_system_option which …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More