关于修改表结构的问题.

[lizhenlz]

请教一个问题 在sqlserver中定义了一个自增列
现在我已经定义了一个主键,我想将自增的属性去掉呢?不能为自增列，因为分页时也用到自增列.所以想请教，怎样将自增列属性去掉.也就是将identity的去掉,变成只为 int的字段.不为自增列.

具体过程如下:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]
go
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS on
go
----系统部分
CREATE PROCEDURE [dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]
@strTableName nvarchar(50) -----'对应tImport_TempTableName表中的nTableID(最大值+1)
AS
set ANSI_Warnings on
begin

------------'定义变量部分
declare @strIdentity nvarchar(100)
--'自增变量的字段名
declare @strExecSql nvarchar(200)
--'动态执行的sql语句

--'删除自增列
select @strIdentity=[name] from syscolumns where [id] in (select [id]from sysobjects where [name]=@strTableName and autoval is not null)
set @strExecSql=' alter table '+@strTableName+ ' drop column '+@strIdentity
exec (@strExecSql)

--'增加自增列
set @strExecSql=' alter table '+@strTableName+' Add column_seq int identity CONSTRAINT column_seq_'+ltrim(rtrim(@strTableName))+'_pk '
set @strExecSql=@strExecSql+' PRIMARY KEY '
exec(@strExecSql)

--'将identity属性去掉,将设为主键
set @strExecSql=' alter table '+@strTableName+' ' ?


end

[lizhenlz]

alter table a add column_seq int identity(1,1) constraint column_seq_a_pk primary key
go
alter table a add column2 int
go
update a set column2=column_seq
go
alter table a drop constraint column_seq_a_pk
go
alter table a drop column column_seq
go
exec sp_rename 'a.column2','column_seq'
go
alter table a alter column column_seq int not null
go
alter table a add constraint column_seq_a_pk primary key clustered( column_seq) on [primary]
go
这样就搞定啦.谢谢你们!一会就结.

[lizhenlz]

整个存储过程为:

CREATE PROCEDURE [dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]
@strTableName nvarchar(50) -----'对应tImport_TempTableName表中的nTableID(最大值+1)
AS
set ANSI_Warnings on
begin

------------'定义变量部分
declare @strIdentity nvarchar(100)
--'自增变量的字段名
declare @strExecSql nvarchar(200)
--'动态执行的sql语句

------------'变量初始化
set @strIdentity=''
set @strExecSql=''

--'删除自增列
select @strIdentity=[name] from syscolumns where [id] in (select [id] from sysobjects where [name]=@strTableName and autoval is not null)
if len(ltrim(rtrim(@strIdentity)))>0
begin
set @strExecSql=' alter table '+@strTableName+ ' drop column '+@strIdentity
exec (@strExecSql)
end

----------'增加自增列
set @strExecSql=' alter table '+@strTableName+' Add column_seq int identity(1,1) CONSTRAINT column_seq_'+ltrim(rtrim(@strTableName))+'_pk '
set @strExecSql=@strExecSql+' PRIMARY KEY '
exec(@strExecSql)

----------'将identity属性去掉,将设为主键
--'添加列
set @strExecSql=' alter table '+@strTableName+' Add column_seq_Temp int '
exec(@strExecSql)
--'更新新增列数据
set @strExecSql=' update '+@strTableName+' set column_seq_Temp=column_seq '
exec(@strExecSql)
--'删除关系
set @strExecSql=' alter table '+@strTableName+' drop constraint column_seq_'+ltrim(rtrim(@strTableName))+'_pk '
exec(@strExecSql)
--'删除列
set @strExecSql=' alter table '+@strTableName+' drop column column_seq '
exec(@strExecSql)
--'重新命名列名
set @strExecSql= ' exec sp_rename '''+ltrim(rtrim(@strTableName))+'.column_seq_Temp'',''column_seq'''
exec(@strExecSql)
--'更改属性
set @strExecSql=' alter table '+@strTableName+' alter column column_seq int not null '
exec(@strExecSql)
--'设定为主键
set @strExecSql=' alter table '+@strTableName+' add constraint column_seq_'+ltrim(rtrim(@strTableName))+'_pk '
set @strExecSql=@strExecSql+' primary key clustered( column_seq) on [primary] '
-- print @strExecSql
exec(@strExecSql)

end

GO

[eken]

小子，偷了我的方法，快说出来什么时候偷得...
之前表没有主键
alter table tbname add _nTmpID int identity(1,1)

alter table tbname add column_seq int

update table set column_seq =_nTmpID

alter table tbname drop column =_nTmpID

alter table tbname add CONSTRAINT PK_xxx PRIMARY KEY CLUSTERED (e) on [PRIMARY]

[lizhenlz]

呵呵,大家都帮忙嘛，呵呵。结帖了，再不结，不行了。

[pengdali]

ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null
go
SET IDENTITY_INSERT 表 ON
go
update tablename set 编号1=编号
go
SET IDENTITY_INSERT 表 OFF
go
ALTER TABLE 表 DROP COLUMN 编号
go
exec sp_rename '表.编号1','编号'
go

[pengdali]

发错了！ :(

alter table tablename add id1 int

update tablename set id1=id

alter table tablename drop column id

exec sp_rename 'tablename.id1','id'

[csdn_bob]

up,不会!

[pengdali]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]
go
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS on
go
----系统部分
CREATE PROCEDURE [dbo].[up_Add_Primary_Key_On_ModifyTable_Emms]
@strTableName nvarchar(50) -----'对应tImport_TempTableName表中的nTableID(最大值+1)
AS
set ANSI_Warnings on
begin

------------'定义变量部分
declare @strIdentity nvarchar(100)
--'自增变量的字段名
declare @strExecSql nvarchar(200)
--'动态执行的sql语句

--'删除自增列
select @strIdentity=[name] from syscolumns where [id] in (select [id]from sysobjects where [name]=@strTableName and autoval is not null)
set @strExecSql=' alter table '+@strTableName+ ' drop column '+@strIdentity
exec (@strExecSql)

--'增加自增列
set @strExecSql=' alter table '+@strTableName+' Add column_seq int identity CONSTRAINT column_seq_'+ltrim(rtrim(@strTableName))+'_pk '
set @strExecSql=@strExecSql+' PRIMARY KEY '
exec(@strExecSql)

--'将identity属性去掉,将设为主键
set @strExecSql=' alter table '+@strTableName+' add tempC int ; update '+@strTableName+' set tempC='+@strIdentity+' ; alter table '+@strTableName+' drop column '+@strIdentity+' ; exec sp_rename '''+@strTableName+'.tempC'','''+@strIdentity+''''


end

[happydreamer]

alter table tbname add column_seq int identity(1,1)

alter table tbname add column2 int

update table set column2=column_seq

alter table tbname drop constraint PK__xxx

alter table tbname drop column column_seq

exec sp_rename 'column2','column_seq'