Adjusting the database structure in the SQLServer2000 run

server|sqlserver| Data | database | database structure
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 '
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' username ', n ' user ', n ' dbo ', n ' table ', n ' hr_user ', n ' column ', n ' UserName '
Go
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 DEFAULT (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 '
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' user nickname ', n ' username ', 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 Table [Hr_user] Alter
Column [nickname] [varchar] (m) COLLATE chinese_prc_cs_as not NULL
Go