Adjusting the database structure of SQL Server 2000

Time:2021-4-20

The database structure in the development process will inevitably need to be modified repeatedly. The most troublesome situation is that the developer’s database structure has been modified, and there is a large amount of data in the database in practical application. How to update the data structure without affecting the data in the database? Of course, we can manually adjust the fields added, corrected and deleted in the table structure of the application database one by one. This is relatively simple for one or two fields. If the changes are relatively large, the process will be very cumbersome. This article is intended to introduce the use of SQL Server 2000 T-SQL statements for database structure adjustment, hoping to bring you some convenience. The following is an example of an existing database table_ Take user as an example to explain how to do this kind of operation.

HR_ Existing structure:

[userid] [int] not null, user ID, primary key 
[ UserName ] [ varchar ] ( fifty )  NOT NULL  , User name

1、 Database add new field

Now, it needs to be done in HR_ The user’s nickname [nickname] [varchar] (50) is not empty, and the date of birth] [datetime] is not empty.
In the development database, we have added these two fields. The construction statements for generating new tables in the 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] (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 [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' date of birth ', n' user ', n' dbo ', n' table ', n' HR_ User', N'column', N'Birthday' 
GO 
exec sp_ addextendedproperty N'MS_ Description ', n' user nickname ', 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, let’s construct the modification statement of the application database. T-SQL modifies the table structure and adds new fields. The syntax is alter table tablename add. If we want to add two fields, we should write 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 middle statement is just a simple copy of the two statements corresponding to the two fields in the creation statement. Add two sentences with descriptions and you’re done.

exec sp_ addextendedproperty N'MS_ Description ', n' date of birth ', n' user ', n' dbo ', n' table ', n' HR_ User', N'column', N'Birthday'
GO
exec sp_ addextendedproperty N ' MS_ Description ',  N ' User nickname ',  N ' user ',  N ' dbo ',  N ' table ',  N ' HR_ User ',  N ' column ',  N ' NickName '
GO

  2、 Database modification field
  Now we find that the length of the user name and nickname fields is not enough, and we need to change them to 100


Alter Table [HR_User] Alter
 Column [UserName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULL
GO

Alter Table [HR_User] Alter
 Column [NickName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULL
GO

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]