SQL SERVER – Check If Column Exists in SQL Server Table

A very frequent task among SQL developers is to check if any specific column exists in the database table or not. Based on the output developers perform various tasks. Here are couple of simple tricks which you can use to check if column exists in your database table or not.

Method 1

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
PRINT
'Your Column Exists'
END  

For AdventureWorks sample database

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'Name' AND OBJECT_ID = OBJECT_ID(N'[HumanResources].[Department]'))
BEGIN
PRINT
'Your Column Exists'
END  

Method 2

IF COL_LENGTH('table_name','column_name') IS NOT NULL
BEGIN
PRINT
'Your Column Exists'
END

For AdventureWorks sample database

IF COL_LENGTH('[HumanResources].[Department]','Name') IS NOT NULL
BEGIN
PRINT
'Your Column Exists'
END

Method 3

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'TableName'
AND [COLUMN_NAME] = 'ColumnName'
AND [TABLE_SCHEMA] = 'SchemaName')
BEGIN
PRINT
'Your Column Exists'
END

For AdventureWorks sample database

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'Department'
AND [COLUMN_NAME] = 'Name'
AND [TABLE_SCHEMA] = 'HumanResources')
BEGIN
PRINT
'Your Column Exists'
END

Let me know if you know any other method to find if Column Exists in SQL Server Table.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – Check If Column Exists in SQL Server Table

  1. Hi pinal sir,
    i gone through this article i got little doubt. here your used N in the where clause what is the use of “N” and why we use that in where clause earlier also i have seen this “N” in your posts please tell me sir.i tried this query without “N” in where clause it was also worked fine to me

  2. I prefer the information_schema approach, since this is ANSI. So Oracle and MySQL DBA’s also know the meaning of this statement….

  3. This task is something I have had to do many times. An additional challenge is finding a column on any database on a server. This is the solution I came up with:

    ————————————————————————————
    — Code to search for a string in all databases on a server —
    — R. Shawn Myers 11/28/2011 —
    ————————————————————————————

    CREATE TABLE #DB_NAMES
    (
    DBNAME varchar(255),
    DATABASESIZE int,
    REMARKS varchar(255)
    )

    INSERT #DB_NAMES exec sp_databases

    CREATE TABLE #RESULTS
    (
    DATABASE_NAME varchar(255),
    TABLE_NAME varchar(255),
    COLUMN_NAME varchar(255)
    )

    Begin

    Declare cur cursor for
    SELECT DBNAME FROM #DB_NAMES ORDER BY 1

    Declare @SQL varchar(255)
    Declare @DB_NAME varchar(255)
    Declare @SEARCH_STRING varchar(255)

    open cur
    fetch next from cur into @DB_NAME

    Set @SEARCH_STRING = ‘%Your_Column_Name%’

    WHILE @@FETCH_STATUS = 0

    begin

    set @SQL = ‘INSERT INTO #RESULTS (DATABASE_NAME, TABLE_NAME, COLUMN_NAME)select TABLE_CATALOG as DATABASE_NAME, TABLE_NAME, COLUMN_NAME FROM ‘ +@DB_NAME +’.INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE COLUMN_NAME LIKE ”’ + @SEARCH_STRING + ””

    EXEC(@SQL)

    print @sql

    FETCH NEXT FROM cur INTO @DB_NAME
    end

    SELECT * FROM #RESULTS

    DROP TABLE #DB_NAMES
    DROP TABLE #RESULTS

    CLOSE cur
    DEALLOCATE cur
    END;
    GO

    – Shawn

  4. Hello,
    we can also use Information_schema (correct me if I am wrong)

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME LIKE ‘YOUR_COLUMN_NAME’

    cheers

  5. Hi pinal sir, i got one doubt here this search query don’t works in some cases what i mean is print statements works fine but if you replace print statement with select statement like in my case below
    IF EXISTS(select * from sys.columns where Name =N’securityuserFK’ and Object_id = Object_id(N'[dbo].[securityUser]’))
    BEGIN
    select USERID, UserName,FirstName,LastName
    from securityUser where securityuserFK IS NOT NULL
    END

    When i run this i come up with this errro:
    Invalid column name ‘securityuserFK’.

    I think you can give me the valid reason for this.

  6. Hi pinal sir, i got one doubt here this search query don’t works in some cases what i mean is print statements works fine but if you replace print statement with select statement like in my case below
    IF EXISTS(select * from sys.columns where Name =N’securityuserFK’ and Object_id = Object_id(N’[dbo].[securityUser]‘))
    BEGIN
    select USERID, UserName,FirstName,LastName
    from securityUser where securityuserFK IS NOT NULL
    END

    When i run this i come up with this errro:
    Invalid column name ‘securityuserFK’.

  7. A stored procedure to insert record into department table mantioned below,before inserting should check if departmenrt name already exist…..
    database–Test
    table-Department
    column1—-DepartmentId bigint column2—Name varchar(50)

    create database Test go use Test go create table Department ( DeptId bigint, DeptName varchar(50) ) go create proc insert_dept @id bigint, @name varchar(50) as Insert into Department(DeptId,DeptName)values (@id,@name) go insert_dept 101,’HR’ insert_dept 103,’Design’ insert_dept 104,’Development’ go IF COL_LENGTH(‘[Deptname].[Department]’,’Deptname’) IS NOT NULL BEGIN PRINT ‘Department Name already Exists’ END go insert_dept 104,’HR’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s