Checking for table columns before adding them in SQL Server

Question

I need to script out a table modification at work. Rather than do a simple "if exists then drop and create", they want it to check for the new columns I'm adding, and only then alter the table with them if they don't exist.

Could someone help me with the script? Assume a simple table that looks like this currently:

CREATE TABLE myTable (

[ID] [int] NOT NULL,
[FirstName] [varchar] (20) NOT NULL,
[LastName] [varchar] (20) NOT NULL

)

.. I'd like to add an Address field, varchar(50) let's say, but only if it doesn't already exist in the schema.

Pavel Kutakov · Accepted Answer · 2013-07-26 15:27:13Z

4

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myTable' AND COLUMN_NAME = 'Address')
BEGIN

    ALTER TABLE [dbo].[myTable] ADD 
        [Address] varchar(50) NOT NULL 
END

answered Jul 26, 2013 at 15:27

Pavel Kutakov

9711 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amit · Accepted Answer · 2013-07-26 15:24:19Z

1

Try this

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTable ]') AND type in (N'U'))
BEGIN
 DROP TABLE [dbo].[myTable ]
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE myTable (

[ID] [int] NOT NULL,
[FirstName] [varchar] (20) NOT NULL,
[LastName] [varchar] (20) NOT NULL,
[Address] [varchar] (50) NOT NULL
)

OR

if not exists(select * from sys.columns 
            where Name = N'Address' and Object_ID = Object_ID(N'myTable'))    
begin
    alter table myTable
    add Address varchar(50) NOT NULL
end
GO

answered Jul 26, 2013 at 15:24

Amit

15.4k8 gold badges49 silver badges69 bronze badges

1 Comment

Comfortably Numb Over a year ago

OP doesn't want to DROP the table. Just add the column IF it doesn't exist

Abhishek Jain · Accepted Answer · 2013-07-26 15:51:05Z

1

Try this:

Here using it you can make check for multiple columns and get you table altered...

DECLARE @query VARCHAR(MAX)

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'Address' AND OBJECT_ID = OBJECT_ID(N'<TableName>'))
BEGIN
SET   
    @query = 'ALTER TABLE <TableName> ADD Address varchar(50) GO'
END 

--You can give multiple If conditions, example:
--IF EXISTS(SELECT * FROM sys.columns
--WHERE Name = N'<SomeOtherColumn>' AND OBJECT_ID = OBJECT_ID(N'<TableName>'))
--BEGIN
--SET   
    --@query = @query + 'ALTER TABLE <TableName> ADD <SomeOtherColumn> varchar(50) GO'
--END 

EXEC sp_Executesql @query

edited Jul 26, 2013 at 15:51

answered Jul 26, 2013 at 15:29

Abhishek Jain

2,6271 gold badge20 silver badges12 bronze badges

Comments

Mr.Monshaw · Accepted Answer · 2013-07-26 16:48:41Z

1

IF 'col_name' 
NOT IN 
   SELECT Name
   FROM table_name.columns 
ALTER TABLE table_name 
ADD 'col_name' VARCHAR(65) NOT NULL

something like this?

EDIT: my friend believes this is much better

IF NOT EXISTS 
   (SELECT Name
    FROM table_name.columns
    Where Name='target_column_name')
ALTER TABLE table_name
ADD 'target_column_name' VARCHAR(65) NOTNULL

edited Jul 26, 2013 at 16:48

answered Jul 26, 2013 at 15:28

Mr.Monshaw

4502 silver badges7 bronze badges

Collectives™ on Stack Overflow

Checking for table columns before adding them in SQL Server

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related