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]
Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • sql server
By Hamden On 09 May, 13  Viewed: 476

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 359

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 482

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,157

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 329

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 4,843