如何把数据库中所有表的主键更改为int identifier？

Question

如何把数据库中所有表的主键更改为int identifier？注意数据库表有很多，每个表都是唯一主键，字段为ID，原来是int not null,想改为int identifier(1,1)我知道可以一个表一个表的更改，有没有sql可以更改整个数据库的表？

Answer 1

已表 tableA(id int not null,name varhcar(10))为例
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tableA
(
id int NOT NULL IDENTITY (1, 1),
name varchar(10)
)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tableA ON
GO
IF EXISTS(SELECT * FROM dbo.tableA)
 EXEC('INSERT INTO dbo.Tmp_tableA (id,name)
SELECT id,name FROM dbo.tableA WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tableA OFF
GO
DROP TABLE dbo.tableA
GO
EXECUTE sp_rename N'dbo.Tmp_tableA', N'tableA', 'OBJECT' 
GO
ALTER TABLE dbo.tableA ADD CONSTRAINT
PK_tableA PRIMARY KEY CLUSTERED 
(
id
) ON [PRIMARY]GO
COMMIT

Answer 2

LS的只能更改一个表
我希望可以更改所有的表，因为表太多了。
每个表的主键命名都是ID

Answer 3

SQL Server 2005是否可以更改系统表，我写了一个SQL，不过不能执行update a set a.is_identity=1
from sys.columns a,sys.objects b
where a.name='ID'
and a.object_id=b.object_id
and b.type='U'设置了EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE;也不行，SQL 2000中可以

Answer 4

declare @sql varchar(8000)
set @sql='
declare @sql varchar(8000)
set @sql=''alter table ? drop column id ; alter table  ?  add id int identity(1,1) primary key''
exec(@sql)
'
exec sp_msforeachtable @sql
没数据，试试

Answer 5

DECLARE @s VARCHAR(1000)
DECLARE c CURSOR FOR 
SELECT 'ALTER TABLE ['+a.name+'] DROP COLUMN ID;ALTER TABLE ['+a.name+'] ADD ID INT IDENTITY PRIMARY KEY;'
FROM sysobjects a
JOIN syscolumns b
ON a.id=b.id
WHERE b.name='id'  
AND a.type='U'
OPEN c 
FETCH c INTO @s 
WHILE @@FETCH_STATUS=0
BEGIN 
EXEC (@s)
FETCH c INTO @s
END 
CLOSE c 
DEALLOCATE c
 

Answer 6

可以新建一个表，id 为 identity之外，其他的字段跟你原表一样，然后把原表的数据insert到新建的表中（除了id列），再把原表删除，再把新建的表改名为原表名

Answer 7

已经存在的表没法用sql改 
用企业管理器该一下吧

Answer 8

楼主，一下是你要的sql，测试通过，
CREATE TABLE #TableNameList
(name varchar(100))
INSERT INTO #TableNameList
SELECT o.name FROM sys.objects AS o JOIN sys.columns AS c ON o.object_id=c.object_id WHERE o.[type]='U' AND c.name='id'
WHILE (SELECT COUNT(*) FROM #TableNameList)>0
BEGIN
DECLARE @tableName varchar(100)
SELECT TOP 1 @tableName=name FROM #TableNameList
DECLARE @dropColumnSql nvarchar(100)
DECLARE @addNewColumnSql nvarchar(100)
SET @dropColumnSql = 'ALTER TABLE ' + @tableName + ' DROP COLUMN id'
SET @addNewColumnSql='ALTER TABLE ' + @tableName + ' ADD id INT IDENTITY(1,1)'
exec sp_executesql @dropColumnSql
exec sp_executesql @addNewColumnSql
DELETE FROM #TableNameList WHERE name=@tableName
END
DROP TABLE #TableNameList

Answer 9

无数据,直接在企业管理器中改吧,几百个表也就几个钟头.
要用t-sql,太复杂.