/ * - 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 - * /
/ * - Call example
EXEC P_SETID 'Name,' To convert the field name "- * / create proc p_setid @ tbname sysname, - To process the table name @fdname s1 varchame to convert to the identity field name asDeclare @ s1 varchar 8000, @ S2 varchar (8000), @TMPTB Sysnameselect @ 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 (Name Bigint, Name VARCHAR (10)) Insert INTO Table Select 1, 'Zhang San' Union All Select 2, 'Lee Four' Union All Select 4, 'King Five' Go
- 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
