3 ways to find out if the column in table exists

There are at least 3 ways to find out if the column in table exists in MS SQL server. It's a common task for developers or administrators when you need to modify table.

1. Using COL_LENGTH

IF COL_LENGTH('AllLists', 'tp_Author') IS NOT NULL
    PRINT 'Column Exists'
ELSE
    PRINT 'Column doesn''t Exists'

IF COL_LENGTH('AllLists', 'tp_Author') IS NOT NULL

PRINT 'Column Exists'

ELSE

PRINT 'Column doesn''t Exists'

2. Using sys.columns

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'tp_Author'
          AND Object_ID = Object_ID(N'AllLists'))
    PRINT 'Column Exists'
ELSE
    PRINT 'Column doesn''t Exists'

IF EXISTS(SELECT 1 FROM sys.columns

WHERE Name = N'tp_Author'

AND Object_ID = Object_ID(N'AllLists'))

PRINT 'Column Exists'

ELSE

PRINT 'Column doesn''t Exists'

3. Using INFORMATION_SCHEMA.COLUMNS

IF EXISTS 
(
    SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name = 'AllLists'
    AND column_name = 'tp_Author'
)
    PRINT 'Column Exists'
ELSE
    PRINT 'Column doesn''t Exists'

IF EXISTS

(

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'AllLists'

AND column_name = 'tp_Author'

)

PRINT 'Column Exists'

ELSE

PRINT 'Column doesn''t Exists'

30 August 2017, admin
SQL
Tags: sql, SQL query, Transact-SQL
Comments: (0)

3 ways to find out if the column in table exists

No Comments »

Leave a comment

New posts