Search a column name into all the table across databases in sql server

 How to search a column name in any table (entire database) or how to check a column exists in any table or not, how to check a column belongs to which table, these are very common question we can get on different websites. Some times we know the column name but don't know the column name then we need a script to get the table name to proceed.

Suppose we have table with primary key and want to know what are the other tables where this ID is used so we can relate those tables to get the required information.

In this article, we will see different query to get the column name from entire database as well as in our specified databases

Let's say we want to check for column BlogId then

Script 1: Simplest one by using information_schema.columns

SELECT [table_name]
  , TABLE_SCHEMA [schema_name]
  , [column_name]
FROM information_schema.columns
WHERE column_name like '%BlogId%'
ORDER BY [table_name], [column_name]

Script 2: by using sys.tables and sys.columns

SELECT t.name AS [table_name],
   SCHEMA_NAME(schema_id) AS [schema_name],
   c.name AS [column_name]
FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%blogid%'
ORDER BY  [table_name], [column_name]

Script 3: by using sys.columns and sys.objects

SELECT o.name[table_name]
  ,c.name [column_name]
FROM sys.columns  c
   INNER JOIN sys.objects  o on c.object_id=o.object_id
WHERE c.name like  '%BlogId%'
ORDER BY [table_name], [column_name]

If we have to search the blogId in three different databases say DB1, DB2 and DB3 then we can use TABLE_CATALOG in where clause and provide the name of all the databases where we have to search.

SELECT TABLE_CATALOG [database]
  , [table_name]
  , TABLE_SCHEMA [schema_name]
  , [column_name]
FROM information_schema.columns  
WHERE column_name like '%BlogId%'
    AND TABLE_CATALOG IN ('DB1', 'DB2', 'DB3')
ORDER BY [table_name], [column_name]