Adjusting the database structure in the SQLServer2000 run

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] () 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) COLLATE chinese_prc_cs_as not NULL,
[Birthday] [DateTime] Not NULL
) on [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 ()) for [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_addextendedproperty n ' ms_description ', n ' user nickname ', n ' username ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' nickname '
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' userid ', n ' user ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' UserId '