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]

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 say we have a customers table with following structure and data

Now suppose we have to bind a drop down on page so we need Customer id and FirstName + LastName so we will use

SELECT [CustomerID],   [FirstName] + ' ' + [LastName] AS [CustomerName] FROM Customers 

It will work smooth because first name and last name both have some values no NULL column.

Now let's say, we have to use FirstName, MidleName and LastName as CustomerName, change our query to this one

SELECT [CustomerID],   [FirstName] + ' ' + [MidleName] + ' ' + [LastName] AS [CustomerName] FROM Customers 

It will return

CustomerID  CustomerName
----------- ------------------
10001       Jamey Jo Durham
10002       NULL
10003       NULL
10004       NULL

Is there any mistake we made in our sql statement, no. It is because of the null value in Middle name, so how to fix it? We will check the column value by using ISNULL function and if it is null then we will use blank ('') [we don't want to go here in detail about the differences between blank and null because we are discussing here about concatenation of columns].

SELECT [CustomerID],  
IsNull([FirstName], '') + ' ' + IsNull([MidleName], '') 
   + ' ' + IsNull([LastName], '') AS [CustomerName]
FROM Customers
/* ---  Output  ---- */
CustomerID  CustomerName
----------- -----------------
10001       Jamey Jo Durham
10002       John  Smith
10003       Abhay  Singh
10004       Sunil  Kumar

It looks good but there is a problem, if you will see closely record 2,3 and 4, there are double spaces between first name and last name. It is not so bad, but suppose if we need ot use , in place of space then it will not look good. check the following example.

SELECT [CustomerID],  
[FirstName] + ' ' + [MidleName] + ' ' + [LastName] AS [CustomerName],
[Address1] + ', ' + [Address2] + ', '+ [City] 
  + ', '+ [State] + ', ' + [Country] + '-' + [Zip] AS [Address]
FROM Customers

/* -----------   See Output  ----------- */
CustomerID  CustomerName     Address
----------- ---------------- ----------------------------------------------
10001       Jamey Jo Durham  127 Park Avenue, Lane 10, Jersey City, NJ, USA-10234
10002       NULL             Lane 2, Friends Colony, New Delhi, DL, India-110025
10003       NULL             NULL
10004       NULL             NULL

Now let's use ISNULL function to check the null value and use blank if value is null, so new sql query will be

SELECT [CustomerID],  
IsNull([FirstName], '') + ' ' + IsNull([MidleName], '') + ' ' 
     + IsNull([LastName], '') AS [CustomerName],
IsNull([Address1], '') + ', ' + IsNull([Address2], '') + ', ' 
   + IsNull([City], '') + ', '+ IsNull([State], '') + ', ' 
   + IsNull([Country], '') + '-' + IsNull([Zip], '') AS [Address]

/* --- Output  ----*/
CustomerID  CustomerName     Address
----------- ---------------- ----------------------------------------------------
10001       Jamey Jo Durham  127 Park Avenue, Lane 10, Jersey City, NJ, USA-10234
10002       John  Smith      Lane 2, Friends Colony, New Delhi, DL, India-110025
10003       Abhay  Singh     A-123, T. Appartment, , , MB, -220345
10004       Sunil  Kumar     A-92 C, , New Delhi, , -
FROM Customers

In record 3 and 4 CustomerName have double space one for MiddleName and one for LastName. Similarly, Address column have extra commas, so how to fix them, do we need some logic to first check and then use space or commas. No, we will use Stuff and Coalesce to fix this problem, so change our above query to this one

SELECT [CustomerID],  
Stuff(
     Coalesce('' + [FirstName], '') 
   + Coalesce(' ' + [MidleName], '') 
   + Coalesce(' ' +[LastName], '')
   , 1, 1, '') AS [CustomerName],
Stuff(  
     Coalesce(', ' + [Address1], '') 
   + Coalesce(', ' + [Address2], '') 
   + Coalesce(', ' + [City], '') 
   + Coalesce(', ' + [State], '') 
   + Coalesce(', ' + [Country], '') 
   +Coalesce('-' + [Zip], '')
   , 1, 1, '') AS [Address]
FROM Customers

And here is the final output, is not it what we want