真正MS SQL 2000高手你在哪里：如何写一段SQL语句去掉一个字段的自增长属性??? ( 积分: 50 )

如何写一段SQL语句去掉一个字段的自增长属性，而不是直接在数据库管理工具上面去修改那个字段。也不是把原来的表数据导出到临时表，然后drop掉，再create一个新的table。
也不是用set identity_insert 表名 on
set identity_insert 表名 off
来打开或者关闭identity字段的自增或者自减。

如何写一段SQL语句去掉一个字段的自增长属性，而不是直接在数据库管理工具上面去修改那个字段。也不是把原来的表数据导出到临时表，然后drop掉，再create一个新的table。
也不是用set identity_insert 表名 on
set identity_insert 表名 off
来打开或者关闭identity字段的自增或者自减。

那就用alter來修改表結構呀

to hhmyz
alter不让修改字段的自增长属性！！！！！！！！！！

alter table 表名 alter column 字段名 类型(大小)

to 饭前饭后
alter不让修改字段的自增长属性？？？？？？？？？

兄弟呀,好像不行吧,我见 sql 本身就是先建一个临时表那么做的,你用事件查看器跟踪一下就知道了

首先，不可能直接修改IDENTITY属性字段，只好通过字符字段来过渡，然后再恢复成int字段，以下就是测试代码：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

select *
from test
go

/*必须删除约束*/
alter table test drop CONSTRAINT PK_test
go
alter table test ADD mych VARCHAR(20) NULL
go
update test set mych=id
go

select *
from test
go

alter table test drop COLUMN id
go

select *
from test
go

alter table test ADD id int
go

select *
from test
go

update test set id=mych
go

select *
from test
go

alter table test drop COLUMN mych

select *
from test
go

很简单，改系统表。假设库名为 db1，表名为 tbl1，要改的列名为 col1：
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE syscolumns
SET typestat = 0, colstat = 0
WHERE name = 'col1' and id = OBJECT_ID('db1..tbl1')
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

to vvyang
这样做，当select * from db1..tbl1 表时报如下错误：
未能在 sysobjects 中找到数据库‘tbl1 ’的对象 id '*******'对应的行。
因为在可视化工具更改自增属性时没更改一次时，‘tbl1 ’的对象 id是变化的。
而用如下语句时：
假设库名为 db1，表名为 tbl1，要改的列名为 col1：
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE syscolumns
SET typestat = 0, colstat = 0
WHERE name = 'col1' and id = OBJECT_ID('db1..tbl1')
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
‘tbl1 ’的对象 id是不变化的。

打开“事件探查器”，然后在“企业管理器”中修改表结构并跟踪SQL语句

假定该表TableName有两个字段
ID 自动增长
Name varchar(50)
现在要修改，把ID的自动增长属性去掉，这个很麻烦。代码如下


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_TableName
(
ID int NOT NULL,
Name varchar(50) NOT NULL,
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TableName)
EXEC('INSERT INTO dbo.Tmp_TableName (ID, Name)
SELECT ID, Name FROM dbo.TableName (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TableName
GO
EXECUTE sp_rename N'dbo.Tmp_TableName', N'TableName', 'OBJECT'
GO
COMMIT

To btt 老兄：
难道这个还有副作用么，N 个人用过 1000 遍了。
我说的自增长字段是整型的，标识为“是”那种。

自动增长字段是非常重要得字段，因此不能随便修改。

为什么要修改呢，设计得时候就应该先考虑清楚，以后

修改这样得字段当然是不方便了！

to vvyang
有副作用，时间是检验真理的唯一标准，确实报错。
我说的也是自增长字段是整型的，标识为“是”那种。

楼主：
我前面提供的方法是经过验证的，难道您没有测试？

其次，我后来查询的一些技术资料，象楼主这个问题，完全可以使用sql server自身提供的SQL-DMO组件来解决。详细说明您可以参考sql server在线帮助，以下是它提供的一些代码：
Altering a Table by Adding a Column
These examples illustrate adding columns to an existing Microsoft® SQL Server™ table.

Examples
A. Adding a Column Defined on a Base Data Type
The example illustrates creating a column that does not allow NULL. The provided default value is used to populate existing rows in the table.

Dim tableProducts As SQLDMO.Table

' Create a Column object, then populate it to define a column
' called ShelfLife.
Dim colShelfLife As New SQLDMO.Column
colShelfLife.Name = "ShelfLife"
colShelfLife.Datatype = "smallint"
colShelfLife.AllowNulls = False
colShelfLife.DRIDefault.Text = "31"

' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set tableProducts = _
oSQLServer.Databases("Northwind&quot.Tables("Products&quot

' Mark start of change unit.
tableProducts.BeginAlter

' Add the populated Column object to its containing collection.
tableProducts.Columns.Add colShelfLife

' Create the column by committing the unit of change.
tableProducts.DoAlter

B. Adding a Computed Column
This example illustrates altering a table, adding a column that perform simple multiplication of the values in two other columns.

Dim tableProducts As SQLDMO.Table

' Create a Column object and populate it to define a new column
' called StockValue.
Dim colStockValue As New SQLDMO.Column
colStockValue.Name = "StockValue"
colStockValue.IsComputed = True
colStockValue.Datatype = "money"
colStockValue.ComputedText = "UnitsInStock * UnitPrice"

' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set tableProducts = _
oSQLServer.Databases("Northwind&quot.Tables("Products&quot

' Mark start of change unit.
tableProducts.BeginAlter

' Add the populated Column object to its containing collection.
tableProducts.Columns.Add colStockValue

' Create the column by committing the unit of change.
tableProducts.DoAlter

to chnplzh
非常感谢您的答复。但是您的方法第一：非常复杂；第二是改变了表的字段顺序。

在SQL語句中，不加那個字段就行了。

我原以为很简单的，自己做了试验
打开“事件探查器”，然后在“企业管理器”中修改表结构并跟踪SQL语句
乖乖，呼呼啦啦那么多行，要实现的话可能比较费事。

其实不复杂，简单点（至于字段顺序应该没有关系吧！）
alter table test ADD mych VARCHAR(20) NULL /*增加临时字符字段*/
go
update test set mych=id /*对临时字符字段赋值,等于对应的id*/
go
alter table test drop COLUMN id /*删除id字段*/
go
alter table test ADD id int /*增加id字段，但没有identity属性*/
go
update test set id=mych /*恢复对应的id值*/
go
alter table test drop COLUMN mych /*删除临时字段*/
go