The database structure structure in the development process will inevitably require repeated modifications. The most troublesome situation is that the developer database structure has been modified, and the database has a lot of data in practical applications, how to update the data structure without affecting the data in the database? Of course, we can manually adjust each addition, correction, delete field of the application database table, which is more simple to one or two fields. If the change is relatively large, this process will be very cumbersome. . This article introduces the use of SQLServer2000 T-SQL statements to make database structure adjustments, hoping to bring some convenience to you. The following is an example of existing database table HR_USER, explaining how such an operation is performed.
HR_User existing structure:
[Userid] [int] Not null, user ID, primary key
[Username] [varchar] (50) Not null, user name
First, the database adds a new field now, you need to add a field user nickname in HR_USER [Nickname] [varchar] (50) is not empty, the date of birth [birthday] [datetime] is not empty. In the development database, we have added these two fields, generate new tables in the query analyzer or Enterprise Manager as follows: if ife (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] [varcha] (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 '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '', N '' '' ',' '' Table '' '', n '' '' '' '' '', n '' '' '' '', N '' '' '' 'GoExec Sp_addextendedProperty N' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' MS_DESCRIPTION '' '', N '' 'user nickname' '' ', n' '' '' '' '' '' '' '' '', n '' '' Table '' '', N ' '' HR_USER '' ', N' '' '' '' ', N' '' '' '' '' GoExec sp_addextendedProperty n '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ' '' '', N '' '' '' '' ', n' '' 'Table' '' '', n '' '' HR_USER '' '', N '' '' column '' ', n' '' '' '' '' GoExec sp_addextendedProperty n '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '': User '' '', n '' '' '' '' '' ', N' '' 'HR_USER' '', N '' '' '' '', '', N '' '' '' '' '' '' Go This time, let's build a modified statement that applies the database.
T-SQL Modify Table Structure Add new field syntax for the ALTER TABLE TABLENAME ADD, so we have to add two fields to write: alter table [dbo]. [Hr_user] add [nickname] [varchar] (50) collate chinese_prc_cs_as not Null default ('' '' ''), [birthday] [datetime] NOT NULL DEFAULT (getDate ()) GO actually intermediate statement is just a simple copy to create two sentences in the two fields in the statement. Plus two sentences to add a description, it is very good. EXEC SP_ADDEXTENDEDPROPERTY N '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '', N '' '' ',' '' Table '' '', n '' '' '' '' '', n '' '' '' '', N '' '' '' 'GoExec sp_addextendedProperty n' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ' '', N '' 'user nickname' '' ', n' '' '' '' '' '' '' '' '', n '' '' Table '' '', N ' '' '' '' '' '' '', N '' '' '' '' '' Go I II, Database Modification Field Now We find UserName, Nickname field is not enough, need to be modified to 100 RALTER 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 nullogo
