34,590
社区成员
发帖
与我相关
我的任务
分享
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
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
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
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已表 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