How to come up with a script for modifying an existing table in the database without losing the existing information?

Question

I have an application with a database which they are working well and everything is fine.

Now, I just need to modify one table in the database by adding more columns to it. I am using SQL Server and the database administrator asked me to provide him with the script of modifying that table.

So how to do that?

I am using SQL Server Management Studio and when I click on the table right-click, I used to select script to create, and Management Studio will give me the script. Now, this table has information, so I think I should not use create script for this table.

The new columns should allow null values.

So what should I use?

For your information, here is the script for creating the table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Test](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [No] [char](20) NOT NULL,
    [Date] [smalldatetime] NULL,
    [ProjectType] [char](500) NULL,
    [ProjectPhase] [char](300) NULL,
    [rejected_reason] [varchar](max) NULL,
    [archived_reason] [varchar](max) NULL,
    [forward_to] [varchar](max) NULL,
    [forward_type] [varchar](max) NULL,
    [forward_concern] [varchar](max) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLATestB_No]  DEFAULT ('*') FOR [No]
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLATestB_Status]  DEFAULT ((2)) FOR [Status]
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLATestB_AID]  DEFAULT ((0)) FOR [AID]
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLATestB_Hit]  DEFAULT ((0)) FOR [Hit]
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLATestB_Sent]  DEFAULT ((0)) FOR [Sent]
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLATestB_SentTo]  DEFAULT ((0)) FOR [SentTo]
GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_LLa_Added_To_Cart]  DEFAULT ((0)) FOR [Added_To_Cart]
GO

Examples of the new columns that should be added to this table are:

[rejected_reason] [varchar](max) NULL,
[archived_reason] [varchar](max) NULL,
[forward_to] [varchar](max) NULL,
[forward_type] [varchar](max) NULL,

score 0 · Answer 1 · answered Dec 03 '12 at 08:24

When you've studied SQL Server Books Online on MSDN, it should be a piece of cake - use ALTER TABLE:

 ALTER TABLE dbo.LLa
 ADD [rejected_reason] [varchar](max) NULL

 ALTER TABLE dbo.LLa
 ADD [archived_reason] [varchar](max) NULL

 ALTER TABLE dbo.LLa
 ADD [forward_to] [varchar](max) NULL

 ALTER TABLE dbo.LLa
 ADD [forward_type] [varchar](max) NULL

The question is more: do you REALLY need up to 2 GByte of data (VARCHAR(MAX)) for each of your columns here?? Seems like a bit of overkill!

You should always use the appropriate data type - if that column will only ever hold up to 100 characters - why not use VARCHAR(100) instead of using the max datatype??

How to come up with a script for modifying an existing table in the database without losing the existing information?

1 Answers1