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/*--将表中的某个字段转换成标识字段,并保留原来的值 注意,因为要删除原表,所以,如果表和其他表的关联,这些关联要重新创建--邹建 2003.12--*//*--调用示例 exec p_setid '表名','要转换的字段名' --*/ CREATE PROC P_SETID @tbname sysname, --要处理的表名 @fdname sysname --要转换为标识字段的字段名 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+' into '+@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
--再写个游标来循环调用上面的存储过程,修改所有满足条件的表 declare #tb cursor local for select object_name(id),name from syscolumns a where name='ID' and objectproperty(id,'IsUserTable')=1 and not exists( select 1 from syscolumns where id=a.id and status=0x80) declare @tbname sysname,@fdname sysname open #tb fetch #tb into @tbname,@fdname while @@fetch_status=0 begin exec p_setid @tbname=@tbname,@fdname=@fdname fetch #tb into @tbname,@fdname end close #tb deallocate #tb
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/*--将表中的某个字段转换成标识字段,并保留原来的值 注意,因为要删除原表,所以,如果表和其他表的关联,这些关联要重新创建--邹建 2003.12--*//*--调用示例 exec p_setid '表名','要转换的字段名'
--*/
CREATE PROC P_SETID
@tbname sysname, --要处理的表名
@fdname sysname --要转换为标识字段的字段名
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+' into '+@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
--再写个游标来循环调用上面的存储过程,修改所有满足条件的表
declare #tb cursor local for
select object_name(id),name from syscolumns a
where name='ID' and objectproperty(id,'IsUserTable')=1
and not exists(
select 1 from syscolumns where id=a.id and status=0x80)
declare @tbname sysname,@fdname sysname
open #tb
fetch #tb into @tbname,@fdname
while @@fetch_status=0
begin
exec p_setid @tbname=@tbname,@fdname=@fdname
fetch #tb into @tbname,@fdname
end
close #tb
deallocate #tb