/ * - Convert a field in the table to identification fields and retain the original value
Note that because the original table is to be deleted, these associations are recreated if the associations of the tables and other tables are re-created.
- Zou Jian 2003.12 (Please keep this information) - * /
/ * - Call example
EXEC P_SETID 'Table Name,' To convert the field name - * /
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_setid]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_setid] Go
Create Proc P_Setid @ TBName Sysname, - To process the table name @fdname s1 varchame (8000), @ S2 varchar (8000), @TMPTB sysselect @ S1 = '' , @ S2 = ', @TMPTB =' [TMP _ ' @ TBNAME ' _ Bak] 'SELECT @ S1 = @ S1 ', [' Name '] Case Name when @fdname dam = Identity (Bigint, 1 1) 'else' 'end, @ S2 = @ S2 ', [' Name '] 'from syscolumns where object_id (@tbname) = idselect @ S1 = Substring (@ S1, 2, 8000), @ S2 = Substring (@ S2, 2, 8000) EXEC ('SELECT TOP 0' @ S1 'INTO' @ TMPTB 'from [' @ TBNAME '] set Identity_Insert' @ TMPTB 'OnInsert INTO' @ TMPTB '(' @ S2 ') SELECT' @ S2 'from [' @ TBNAME '] Set Identity_Insert' @ TMPTB 'Off') EXEC ('Drop Table [' @ TBNAME ') EXEC SP_RENAME @ TMPTB, @ TBNameGo
/ * ================================================================================================================================================================ =========== * /
- Test - Create Table Create Table Table (No. Bigint, Name VARCHAR (10)) Insert INTO Table Select 1, 'Zhang San' Union All Select 2, 'Lee Fourth' Union All Select 4, 'Wang 5 'Goo
- Call the stored procedure, change the number field to the identification field exec p_setid 'table', 'number' Go
- Display processing results Select * from table
- Display whether to modify success SELECT NAME from syscolumns where object_id ('table') = id and status = 0x80GO
- Delete Table Table Table
