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