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] [varcha] (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, generating a new table in the query analyzer or Enterprise Manager is as follows: if EXISTS (SELECT * from dbo.sysObjects where id = Object_id (n '[dbo]. [Hr_user] ') And ObjectProperty (ID, n'susertable') = 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'MS_Description ', N' date of birth ', 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'Goexec sp_addextendedProperty N'MS_DESCRIPTION', N 'User ID', N'User ', N'dbo', N'table ', N'HR_USER', N'Column ', N'Userid'goexec sp_addextendedproperty N'MS_DEXTENDEDPROPERTY N'MS_DESCRIPTION', N 'User Name', N'User ', N'dbo', N 'Table', N'HR_User ', N'Column', N'Usename'go This time, let's build a modified statement that applies the database, T-SQL modification table structure Add new field syntax for the ALTER TABLE TABLENAME ADD, so we want This should be written like this: ALTER TABLE [DBO]. [Hr_user] add [nickname] [varchame] (50) collate chinese_prc_cs_as not null default (''), [birthday] [datetime] not null default (Getdate) )) The GO actually the intermediate statement is just a simple copy to create two sentences in the corresponding two fields. Plus two sentences to add a description, it is very good. exec sp_addextendedproperty N'MS_Description ', N' date of birth ', N'user', N'dbo ', N'table', N'HR_User ', N'column', N'Birthday'GOexec sp_addextendedproperty N'MS_Description ', N 'User Nickname', N'TER ', N'dbo', N'column ', N'NickName'go 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] [varcha] (100) collate chinese_prc_cs_as not nullgoalter table [hr_user] alter column [nickname] [varchar] (100) collate chinese_prc_cs_as not nullogo
