Published on: 12 February, 2018

How to check if a column exists in a Sql Server table?

Tumblr Digg Google Plus

If you have a situation where you want to add a column into a table in SQL Server, it would be a good idea to perform a check first if a column has already existed in a table. Otherwise, if you try to add a duplicate column, you may get an error message when you try to add a column via SQL query.

So our intention is to perform a check to see if a column has already existed in a SQL table if it is not then we are going to perform some action. You can use the following SQL query.

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'columnName'AND Object_ID = Object_ID(N'schemaName.tableName'))
    BEGIN
      -- You can perform your method of action in here.
    END

Let says we want to add a column into our table, we then can add the extra following query to add a new column.

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'columnName'AND Object_ID = Object_ID(N'schemaName.tableName'))
    BEGIN
           ALTER TABLE schemaName.tableName
           ADD NewField1 nvarchar(100),
           NewField2 nvarchar(200)
    END

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.