Adjust the database structure in SQLServer2000 run _mssql

The database structure in the process of development will inevitably require repeated modifications. The most troublesome situation is that the developer database structure has been modified, and the actual application of the database has a large number of data, how to do not affect the data in the database, update data structure? Of course, we can manually apply the database table structure to add, correct, delete the field one by one adjustment, which for one or two fields, is relatively simple, if the change is larger, this process will be very cumbersome. This article is intended to introduce the use of SQLServer2000 T-SQL statement database structure adjustment, hope to bring some convenience to you. The following is an example of an existing database table, Hr_user, to explain how to do such an operation.

Hr_user Existing structure:

[UserId] [INT] Not NULL, user ID, primary key 
[UserName] [varchar] (s) not NULL, user name 

One, the database to add a new field

Now you need to add a field to the Hr_user user nickname [nickname] [varchar] (50) is not NULL, and the date of birth [birthday] [datetime] is not empty.
In the development database we have added these two fields, and the construction statements for generating new tables in Query Analyzer or Enterprise Manager are as follows:

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Hr_user] and OBJECTPROPERTY (ID, N ' isusertable ') = 1) drop table [dbo]. 
[Hr_user] Go CREATE TABLE [dbo]. [Hr_user] ([UserId] [int] NOT NULL, [UserName] [varchar] (m) COLLATE chinese_prc_cs_as not NULL, [nickname] [varchar] (m) CO Llate chinese_prc_cs_as NOT NULL, [birthday] [datetime] is not NULL in [PRIMARY] go ALTER TABLE [dbo]. [Hr_user] ADD CONSTRAINT [Df_hr_user_userid] Default (0) for [UserId], CONSTRAINT [df_hr_user_username] Default (") for [UserName ], CONSTRAINT [df_hr_user_nickname] Default (') for [nickname], CONSTRAINT [Df_hr_user_birthday] Default (GETDATE ()) FO  R [Birthday], CONSTRAINT [Pk_hr_user] PRIMARY KEY CLUSTERED ([UserId]) on [PRIMARY] go EXEC sp_addextendedproperty N ' ms_description ', n ' Birth date ', n ' user ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' birthday ' go exec sp_addextendedpropert Y n ' ms_description ', n ' user nicknames ', N ' users ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' NickName ' go EXEC sp_addextendedproperty n ' ms_description ', n ' user ID ', n ' username ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' use 
 RId '

At this point, we're going to build the modified statement for the application database, and the T-SQL Modify table structure adds a new field syntax for ALTER TABLE TableName add so that we can add two fields so that we should write this:

Alter TABLE [dbo]. [Hr_user] Add
 [nickname] [varchar] (m) COLLATE chinese_prc_cs_as NOT null DEFAULT ("),
 [birthday] [datetime] NOT NULL DEFA ULT (GETDATE ()) Go

In fact, the middle of the statement is simply a copy of the creation of the statement corresponding to the two fields of two sentences. Add two sentences to the description, and you're done.

EXEC sp_addextendedproperty n ' ms_description ', n ' Birth date ', n ' user ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' birthday ' C6/>go
exec sp_addextendedproperty n ' ms_description ', n ' username ', n ' user ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', N ' nickname ' Go

Second, Database modification field
Now we find that the username and nickname fields are not long enough to be modified to 100

Alter Table [Hr_user] Alter
 Column [UserName] [varchar] (m) COLLATE chinese_prc_cs_as not NULL
go

alter TAB Le [Hr_user] Alter
 Column [nickname] [varchar] (m) COLLATE chinese_prc_cs_as not NULL
go