SQL SERVER: How to check whether column exists in table or not?

Introduction: In this article I am going to explain four methods to check whether column exists in sql table or not. 

In previous articles I have explained how to Search particular column in all tables or views in sql server database and Create, check and drop foreign key constraint on table and Query to search any text in all stored procedures,views and functions and Query to delete or select first or last n records from table and Use of self joins in sql with example

Implementation: Let's create a demo table for demonstration purpose using the following script.

CREATE  TABLE dbo.tbEmployeeMaster

(

            EmployeeId     INT IDENTITY(1,1) PRIMARY KEY,

            Name              VARCHAR(100),

            Salary              DECIMAL(18,2),

             Age                   INT  

)

--Insert some dummy data into the table

GO

INSERT INTO dbo.tbEmployeeMaster

VALUES

('Aman',34000,28),

('Rohan',48000,34),

('Varun',80000,25),

('Arjun',37000,45),

('Raghav',22000,33),

('Sameer',12000,27);

--Check data in table

SELECT * FROM dbo.tbEmployeeMaster 

EmployeeId	Name	Salary	Age
1	Aman	34000.00	28
2	Rohan	48000.00	34
3	Varun	80000.00	25
4	Arjun	37000.00	45
5	Raghav	22000.00	33
6	Sameer	12000.00	27

Let’s write the scripts for finding whether column is present in table or not.

I have listed 4 methods to check for columns existence as below.  

Using INFORMATION_SCHEMA.COLUMNS

One of the methods to check for the column in a table is to use INFORMATION_SCHEMA system table for column system view.

Execute the following:

            SELECT  * FROM INFORMATION_SCHEMA.Columns

            WHERE

            TABLE_SCHEMA='dbo' AND

            TABLE_NAME = 'tbEmployeeMaster' AND

            COLUMN_NAME = 'Age'

If the query returns record, then the column is available in the table otherwise columns does not exists.

Using SYS.COLUMNS

We can also use the SYS.COLUMNS system table to check if column exists in a table or not.

Execute the following:

   SELECT  *

    FROM SYS.COLUMNS

    WHERE OBJECT_ID = OBJECT_ID('dbo.tbEmployeeMaster')

    AND NAME = 'Age'

If the query returns record, then the column is available in the table otherwise column does not exists.

Using COL_LENGTH

This function returns the length of the column if it exists in the table. If not, it will return NULL.

Execute the following:

IF COL_LENGTH('dbo.tbEmployeeMaster', 'Age') IS NOT NULL

BEGIN

PRINT 'Column Exists'

END

ELSE

BEGIN

            PRINT 'Column doesn''t Exists'

END

Using COLUMNPROPERTY

This function returns the value of the column’s property if it exists in the table. If not, it will return NULL.

Execute the following:

IF COLUMNPROPERTY(OBJECT_ID('dbo.tbEmployeeMaster'), 'Age', 'ColumnId') IS NOT NULL

BEGIN

PRINT 'Column Exists'

END

ELSE

BEGIN

            PRINT 'Column doesn''t Exists'

END

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post