How To Check If Column Exists for SQL Table in Database

How to check if column exists for SQL table in database

As developers for SQL Server, we frequently need to determine whether a field is present in a particular table or in all tables within the database. We could even need to identify the database tables that contain a certain column.

The information schema for column system view is the simplest and most basic approach to find a column in a table. Execute the below-described select query for INFORMATION SCHEMA.COLUMNS. The column is present in the table if the query returns a record.

USE SampleDB

GO

--Create SampleDB Table

 

CREATE TABLE dbo.SampleTB

  (

     ID      INT IDENTITY(1, 1),

     Name    VARCHAR(100),

     Address VARCHAR(100)

  )

GO

--Create SampleDB View

 

CREATE VIEW dbo.vw_SampleDB

AS

  SELECT ID,

         NAME,

         Address

  FROM   dbo.SampleTB

 

--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%'