What's the correct SQL Server syntax for a script that will create a table if it doesn't exist or else alter it to add new columns?

Question

Not sure if I should have gone to DBA.StackExchange with this but I'm a software developer needing to add columns to an existing back-end database table called T with existing columns I, J, and K (all of type INT). This is so that my new front-end code will work. The table has two existing foreign keys FK_T_U, and FK_T_V, referring to other related tables U, V. All I'm doing is adding three new true/false BIT columns (X, Y, Z) to the table but I need a script to do this. Simple enough! I'm using SQL Server 2014. There's already a CREATE TABLE script present in our source repository which I thought I'd surround with an IF EXISTS and an ELSE statement and insert an ALTER TABLE sequence in the midst. This way, a future redeployment could simply ALTER the existing table, or create it if it was in fact absent. Good idea? I'm having a little trouble getting there so I thought I'd speed this effort up by asking for help on StackOverflow. Your assistance is much appreciated! Here's the existing [working] CREATE script:

BEGIN TRANSACTION
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T]
CREATE TABLE [dbo].[T] (
    [I] [int] IDENTITY (1, 1) NOT NULL ,
    [CourseK] [int] NULL ,
    [I] [int] NOT NULL ,
    [K] [int] NULL ,
    CONSTRAINT [PK_T] PRIMARY KEY  CLUSTERED 
    (
        [I]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GRANT  SELECT  ON [dbo].[T]  TO [OtherApplication]
GRANT  SELECT ,  INSERT  ON [dbo].[T]  TO [AnotherApplication]

ALTER TABLE [dbo].[T] WITH NOCHECK ADD CONSTRAINT [FK_T_U] FOREIGN KEY 
    (
        [K]
    ) REFERENCES [U] (
        [K]
    )

ALTER TABLE [dbo].[T] WITH NOCHECK ADD CONSTRAINT [FK_T_V] FOREIGN KEY 
    (
        [I]
    ) REFERENCES [V] (
        [I]
    )

COMMIT TRANSACTION

Answer 1

BEGIN TRANSACTION

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.name='T')
BEGIN
    CREATE TABLE [dbo].[T] (
        [I] [int] IDENTITY (1, 1) NOT NULL ,
        [CourseK] [int] NULL ,
        [I] [int] NOT NULL ,
        [K] [int] NULL ,
        CONSTRAINT [PK_T] PRIMARY KEY  CLUSTERED 
        (
        [I]
        )  ON [PRIMARY] 
    ) ON [PRIMARY]
    GRANT  SELECT  ON [dbo].[T]  TO [OtherApplication]
    GRANT  SELECT ,  INSERT  ON [dbo].[T]  TO [AnotherApplication]

    ALTER TABLE [dbo].[T] WITH NOCHECK ADD CONSTRAINT [FK_T_U] FOREIGN KEY 
    (
        [K]
    ) REFERENCES [U] ([K])

    ALTER TABLE [dbo].[T] WITH NOCHECK ADD CONSTRAINT [FK_T_V] FOREIGN KEY 
    (
        [I]
    ) REFERENCES [V] ([I])
END

IF NOT EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.columns c ON t.object_id=c.object_id WHERE t.name='T' and c.name='X')
BEGIN
    ALTER TABLE [T] ADD [X] BIT;
END

IF NOT EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.columns c ON t.object_id=c.object_id WHERE t.name='T' and c.name='y')
BEGIN
    ALTER TABLE [T] ADD [Y] BIT;
END

IF NOT EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.columns c ON t.object_id=c.object_id WHERE t.name='T' and c.name='Z')
BEGIN
    ALTER TABLE [T] ADD [Z] BIT;
END

COMMIT TRANSACTION