/*--將表中的某個字段轉換成標識字段,并保留原來的值
注意,因為要刪除原表,所以,如果表和其他表的關聯,這些關聯要重新創建
--鄒建 2003.12--*/
/*--調用示例
exec p_setid '表名','要轉換的字段名'
--*/
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, --要處理的表名
@fdname sysname --要轉換為標識字段的字段名
as
declare @s1 varchar(8000),@s2 varchar(8000),@tmptb sysname
select @s1='',@s2='',@tmptb='[tmp_'[email protected]+'_bak]'
select @[email protected]+',['+name+']'
+case name when @fdname then '=identity(bigint,1,1)' else '' end
,@[email protected]+',['+name+']'
from syscolumns where object_id(@tbname)=id
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
exec('select top 0 '[email protected]+' into '[email protected]+' from ['[email protected]+']
set identity_insert '[email protected]+' on
insert into '[email protected]+'('[email protected]+') select '[email protected]+' from ['[email protected]+']
set identity_insert '[email protected]+' off
')
exec('drop table ['[email protected]+']')
exec sp_rename @tmptb,@tbname
go
/*==========================================================*/
--使用測試
--創建測試的表
create table 表(編號 bigint,姓名 varchar(10))
insert into 表
select 1,'張三'
union all select 2,'李四'
union all select 4,'王五'
go
--調用存儲過程,將編號字段改為標識字段
exec p_setid '表','編號'
go
--顯示處理結果
select * from 表
--顯示是否修改成功
select name from syscolumns
where object_id('表')=id and status=0x80
go
--刪除測試
drop table 表
國內最大的酷站演示中心!
