Converts a field in a table to an identity field and retains the original value

Convert


/*--converts a field in a table to an identity field and retains the original value





Note that because you want to delete the original table, if the table is associated with another table, these associations will re-create the





--Jiangjian 2003.12--*/





/*--Call Example





exec p_setid ' table name ', ' field name to convert '


--*/





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--Name of the table to be processed


@fdname sysname-the field name to convert to the standard literacy segment


as


DECLARE @s1 varchar (8000), @s2 varchar (8000), @tmptb sysname


Select @s1 = ', @s2 = ', @tmptb = ' [tmp_ ' + @tbname + ' _bak] '


Select @s1 = @s1 + ', [' +name+ '] '


+case name when @fdname then ' =identity (bigint,1,1) ' Else ' "End


, @s2 = @s2 + ', [' +name+ '] '


From syscolumns where object_id (@tbname) =id


Select @s1 =substring (@s1, 2,8000), @s2 =substring (@s2, 2,8000)


exec (' select top 0 ' + @s1 + ' in ' + @tmptb + ' from [' + @tbname + ']


SET Identity_insert ' + @tmptb + ' on


insert INTO ' + @tmptb + ' (' + @s2 + ') Select ' + @s2 + ' from [' + @tbname + ']


SET Identity_insert ' + @tmptb + ' off


')


exec (' drop table [' + @tbname + '] ')


exec sp_rename @tmptb, @tbname


Go





 





/*==========================================================*/





--Using Test





--Create a test table


CREATE TABLE (number bigint, name varchar (10))


INSERT INTO Table


Select 1, ' John '


UNION ALL Select 2, ' Dick '


UNION ALL Select 4, ' Harry '


Go





-invokes the stored procedure, changing the number field to the Identity field


exec p_setid ' table ', ' numbered '


Go





--Display processing results


select * FROM table





--Displays whether the modification was successful


select name from syscolumns


where object_id (' table ') =id and status=0x80


Go





--Delete test


drop table