将表中的某个字段转换成标识字段,并保留原来的值

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

/*--将表中的某个字段转换成标识字段,并保留原来的值

 注意,因为要删除原表,所以,如果表和其他表的关联,这些关联要重新创建

--邹建2003.12--*/

/*--调用示例

 execp_setid'表名','要转换的字段名'
--*/

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_setid]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
 dropprocedure[dbo].[p_setid]
GO

CREATEPROCP_SETID
@tbnamesysname, --要处理的表名
@fdnamesysname  --要转换为标识字段的字段名
as
declare@s1varchar(8000),@s2varchar(8000),@tmptbsysname
select@s1='',@s2='',@tmptb='[tmp_'+@tbname+'_bak]'
select@s1=@s1+',['+name+']'
 +casenamewhen@fdnamethen'=identity(bigint,1,1)'else''end
 ,@s2=@s2+',['+name+']'
fromsyscolumnswhereobject_id(@tbname)=id
select@s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
exec('selecttop0'+@s1+'into'+@tmptb+'from['+@tbname+']
setidentity_insert'+@tmptb+'on
insertinto'+@tmptb+'('+@s2+')select'+@s2+'from['+@tbname+']
setidentity_insert'+@tmptb+'off
')
exec('droptable['+@tbname+']')
execsp_rename@tmptb,@tbname
go

 

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

--使用测试

--创建测试的表
createtable表(编号bigint,姓名varchar(10))
insertinto表
select1,'张三'
unionallselect2,'李四'
unionallselect4,'王五'
go

--调用存储过程,将编号字段改为标识字段
execp_setid'表','编号'
go

--显示处理结果
select*from表

--显示是否修改成功
selectnamefromsyscolumns
whereobject_id('表')=idandstatus=0x80
go

--删除测试
droptable表

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>