Convert normal columns to ID Columns

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_toidentity] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_toidentity]
Go

/* -- Convert a common column to an ID column

Modify the table name and create a table according to the new rule.
Only applicable to tables not associated with other objects
After conversion, the related objects of the column are not restored.
Therefore, this processing has great limitations and is only for reference.

-- Producer build 2005.05 (reference please keep this information )--*/

/* -- Call example

Exec p_toidentity 'tb', 'id'

-- Use test
 
-- Create a test table
Create Table Tb (bigint, varchar (10 ))
Insert TB select 1, 'zhang san'
Union all select 2, 'Li si'
Union all select 4, 'wang wu'
Go
 
-- Call the stored procedure and change the number field to the ID field
Exec p_toidentity 'tb', 'number'
Go
 
-- Display the processing result
Select * from TB
 
-- Display whether the modification is successful
Select name from syscolumns
Where object_id ('tb') = ID and status = 0x80
Go
 
-- Delete test
Drop table TB
--*/
Create proc p_toidentity
@ Tablename sysname, -- Name of the table to be processed
@ Fieldname sysname, -- Name of the column to be converted, which must be an integer data field
@ Increment Int = 1 -- incremental id value
As
If isnull (objectproperty (object_id (@ tablename), N 'isusertable'), 0) = 0
Begin
Raiserror ('"% s" must be an existing user table in the current database', @ tablename)
Return
End

-- Mark column conversion check
Declare @ S1 nvarchar (1000), @ S2 nvarchar (4000), @ bktablename sysname
Select @ S2 = '', @ bktablename = cast (newid () as char (36 )),
@ S1 = case
When C. Status = 0x80
The then n' table "% s" already contains the '+ quotename (C. Name, N '"')
+ N', the column "% s" cannot be converted into an ID column'
When T. name like '% int'
Then n', '+ quotename (C. Name)
+ N' = identity ('+ quotename (T. Name)
+ N', 1, '+ Cast (@ increment as varchar) + N ')'
When C. Scale = 0 and T. Name in (N 'decimal', N 'numeric ')
Then n', '+ quotename (C. Name)
+ N' = identity ('+ quotename (T. Name)
+ N' ('+ Cast (C. prec as varchar) + N ')'
+ N', 1, '+ Cast (@ increment as varchar) + N ')'
Else n' table "% s" column "% s" Definition "'+ quotename (T. Name)
+ Case when C. Scale> 0
Then n' ('+ Cast (C. prec as varchar)
+ N', '+ Cast (C. Scale as varchar) + N ')'
Else n'' end
+ N' invalid, cannot be converted to the ID column'
End
From sysobjects o, syscolumns C, policypes t
Where o. Name = @ tablename
And O. ID = C. ID
And C. xusertype = T. xusertype
And (C. Name = @ fieldname or C. Status = 0x80)
If @ rowcount = 0
Begin
Raiserror (n 'table "% s" does not contain columns "% s"', @ tablename, @ fieldname)
Return
End
If left (@ S1, 1) <> ','
Begin
Raiserror (@ S1, 12,16, @ tablename, @ fieldname)
Return
End

-- Generate the column to be processed
Select @ S2 = @ S2 + N', '+ quotename (C. Name)
From sysobjects o, syscolumns C
Where o. Name = @ tablename
And O. ID = C. ID
Order by C. colid
Select @ S1 = Replace (@ S2 + N', ', n',' + quotename (@ fieldname) + N', ', @ S1 + N ','),
@ S1 = substring (@ S1, 2, Len (@ S1)-2 ),
@ S2 = stuff (@ S2, n '')

-- Modify the original table name
Exec sp_rename @ tablename, @ bktablename

-- Convert to ID column Processing
Select @ tablename = quotename (@ tablename ),
@ Bktablename = quotename (@ bktablename)
Exec ('
Select '+ @ S1 + 'input' + @ tablename + N'
From '+ @ bktablename +' where 1 = 0
Set identity_insert '+ @ tablename + N' on
Insert '+ @ tablename + N' (' + @ S2 + N ')
Select '+ @ S2 + N' from' + @ bktablename + N'
Set identity_insert '+ @ tablename + N' off
Drop table '+ @ bktablename)
Go