Adjust the SQL server running database structure and SQL Server database

Adjust the SQL server running database structure and SQL Server database

The database structure during development must be modified repeatedly. The most troublesome case is that the developer's database structure has been modified, and there is a large amount of data in the database in the actual application. How can we update the data structure without affecting the data in the database? Of course, we can manually adjust each field added, corrected, and deleted in the table structure of the application database. This is relatively simple for one or two fields. If the changes are large, this process will be very cumbersome. This article is intended to introduce the use of SQLServer2000 T-SQL statement for database structure adjustment, hope to bring you some convenience. The following uses the existing database table HR_User as an example to explain how to perform such operations.

Existing structure of HR_User:

[UserId] [int] not null, user ID, primary key [UserName] [varchar] (50) not null, user name

1. Add new fields to the database

Now, you need to add the field user NickName [NickName] [varchar] (50) to HR_User. The birth date [Birthday] [datetime] is not empty.
We have added these two fields to the development database. The following statements generate a new table in the query analyzer or Enterprise Manager:

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] (50) COLLATE Chinese_PRC_CS_AS not null, [NickName] [varchar] (50) COLLATE Chinese_PRC_CS_AS not null, [Birthday] [datetime] not null) ON [PRIMARY] go alter table [dbo]. [HR_User] add constraint [distinct] 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 'birthdate ', N 'user', N 'dbo', N 'table ', N 'hr _ user', N 'column', N 'birthday' GO exec sp_addextendedproperty N 'Ms _ description', N 'user nicknames ', N 'user ', N 'dbo', N 'table', N 'hr _ user', N 'column', N 'nickname' GO exec sp_addextendedproperty N 'Ms _ description ', N 'user id', N 'user', N 'dbo', N 'table', N 'hr _ user', N 'column ', N 'userid'

At this time, we will build the modification statement of the application database, the T-SQL to modify the TABLE structure to Add the new field syntax for Alter TABLE TableName Add, so we need to Add two fields should be written like this:

Alter TABLE [dbo].[HR_User] Add [NickName] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL DEFAULT(''), [Birthday] [datetime] NOT NULL DEFAULT(getdate())GO

In fact, the statement in the middle is just two sentences corresponding to two fields in the simple copy creation statement. You can simply add two statements to the description.

Exec sp_addextendedproperty N 'Ms _ description', N 'birthdate ', N 'user', N 'dbo', N 'table', N 'hr _ user ', N 'column', N 'birthday' GOexec sp_addextendedproperty N 'Ms _ description', N 'user nickname ', N 'user', N 'dbo', N 'table ', N 'hr _ user', N 'column', N 'nickname' GO

2. Modify fields in the database
Now we find that the length of the UserName and NickName fields is not enough and we need to change it to 100.

Alter Table [HR_User] Alter Column [UserName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULLGOAlter Table [HR_User] Alter Column [NickName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULLGO