TSQL - How To Check If Column Exists for SQL Table/ View in Database


On daily basis, we come across this scenario when we have to find out if a column exists for
a Table or View in Database. Specially if we are debugging SQL Server Reports/An application,
we take a column name or part of it and want to know that from which table this column is used.

We can query INFORMATION_SCHEMA.COLUMNS view to get this information.

USE Test
GO--Create Test Table
CREATE TABLE dbo.Test
  (
     ID      INT IDENTITY(1, 1),
     Name    VARCHAR(100),
     Address VARCHAR(100)
  )
GO
--Create Test View
CREATE VIEW dbo.vw_TestAS
  SELECT ID,
         NAME,
         Address
  FROM   dbo.Test 

--Check if any of the object( Table,View) has column name='Address'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='ADDRESS'

Or If you want to search with some part of column name

SELECT * FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  COLUMN_NAME LIKE '%ADD%'

Fig 1: How to Check if Column Exists for a SQL Server Table Or View in SQL Server Database









Video Demo for Information_Schema.Columns System View


No comments:

Post a Comment