IMG-LOGO

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

andy - 12 Feb, 2018 7752 Views 0 Comment

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.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...