该过程是因业务需要需要重构所有表结构--为实现取消表上的自增属性，索引结构保持不变而开发的一套脚本；
DECLARE @tablename SYSNAME
DECLARE @i INT=1
DECLARE @count INT
SELECT @count = Count(*)
FROM sys.tables
WHERE type = 'U'
AND type_desc = 'USER_TABLE'WHILE @i<=@count
BEGIN
SELECT @tablename=name FROM (SELECT Row_number() OVER(ORDER BY name )rn,name FROM sys.tables WHERE type='U' AND type_desc='USER_TABLE')a WHERE rn=@i
EXEC Pro_alter_identity @tablename
SET @i=@i+1
END
GO
IF EXISTS(SELECT name
FROM sysobjects
WHERE NAME = 'Pro_alter_identity'
AND type = 'P')
DROP PROCEDURE PRO_ALTER_IDENTITY
GO
-- =============================================
-- Author: zhaowenzhong
-- Create date: 2015.03.30
-- Description: 取消自增字段的自增属性，其他属性保持不变
-- =============================================
CREATE PROCEDURE Pro_alter_identity
(
@TABLE SYSNAME='Feed_Test'
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ERROR INT=0
DECLARE @IDENT_COLUMN NVARCHAR(128)
DECLARE @CONSTRAINT_NAME NVARCHAR(128)
DECLARE @INDEX_KEYS NVARCHAR(512)
DECLARE @I INT=1
DECLARE @COUNT INT
DECLARE @tmp_index_name NVARCHAR(512)
DECLARE @tmp_index_keys NVARCHAR(1024)
DECLARE @DEL_INDEX_SQL NVARCHAR(1280)
DECLARE @CREATE_INDEX_SQL NVARCHAR(1280)
DECLARE @ALTER_SQL NVARCHAR(1280)
DECLARE @UPDATE_SQL NVARCHAR(1280)
DECLARE @DROP_CONS_SQL NVARCHAR(1280)
DECLARE @DROP_COLUMN_SQL NVARCHAR(1280)
DECLARE @RENAME_COLUMN_SQL NVARCHAR(1280)
DECLARE @ADD_CONSTRAINT_SQL NVARCHAR(1280)
DECLARE @INDEX_INCLUDE_KEYS NVARCHAR( 512)
---查找具有自增字段的对应的约束（索引）
SELECT @IDENT_COLUMN = a. name
FROM syscolumns a
INNER JOIN sysobjects b
ON a.id = b.id
AND b.xtype = 'U'
AND b.name <> 'dtproperties'
WHERE b.name = @TABLE
AND Columnproperty (a.id,
a.name,
'IsIdentity') = 1
IF @IDENT_COLUMN IS NULL
BEGIN
RETURN;
END
---临时存储表上索引属性的信息
CREATE TABLE #ALL_INDEX
(
ID INT IDENTITY ( 1, 1 ) NOT NULL PRIMARY KEY ,
INDEX_NAME VARCHAR( 512),
INDEX_DESCRIPTION VARCHAR (512),
INDEX_KEYS VARCHAR( 512),
INDEX_INCLUDE VARCHAR( 512)
)
--获取表结构信息
INSERT INTO #ALL_INDEX (INDEX_NAME, INDEX_DESCRIPTION,INDEX_KEYS )
EXEC Sp_helpindex @TABLE
------- 获取包含索引的字段
DECLARE @Include VARCHAR( 1000)
DECLARE @xy INT
DECLARE @max_count INT
DECLARE @Update_Include_SQL NVARCHAR( MAX)
DECLARE @dbname sysname
SET @dbname ='[' + Db_name () + ']'
SELECT @xy = 1 ,
@max_count = MAX(ID )
FROM #ALL_INDEX
WHILE @xy <= @max_count
BEGIN
SET @Update_Include_SQL =N'
set @Include=null
select @Include=isnull(@Include+'','','''')+c.name
from '
+ @dbname
+ '.sys.columns C join
(select column_id,index_column_id from '
+ @dbname
+ '.sys.index_columns
where object_id=(select object_id from '
+ @dbname + '.sys.tables where name like '''
+ @TABLE
+ ''') and index_id=
(select index_id from '
+ @dbname + '.sys.indexes where name='''
+ ( SELECT INDEX_NAME
FROM #ALL_INDEX
WHERE id = @xy)
+ ''' AND OBJECT_ID=OBJECT_ID(N''' + @TABLE
+ '''))
and is_included_column=1) t on c.object_id=(select object_id from '
+ @dbname + '.sys.tables where name like '''
+ @TABLE
+ ''') and c.Column_id=t.column_id
order by t.index_column_id'
EXEC Sp_executesql @Update_Include_SQL, N'@Include varchar(1000) out',@Include out
UPDATE #ALL_INDEX
SET Index_Include = Isnull ( @Include,
'')
WHERE ID = @xy
SET @xy =@xy + 1
END
--获取自增属性信息
SELECT @CONSTRAINT_NAME = INDEX_NAME,
@INDEX_KEYS = INDEX_KEYS
FROM (SELECT INDEX_NAME,
CASE RIGHT(INDEX_KEYS,
3)
WHEN '(-)' THEN LEFT(INDEX_KEYS,
Len(INDEX_KEYS) - 3)
ELSE INDEX_KEYS
END INDEX_KEYS
FROM #ALL_INDEX
WHERE INDEX_DESCRIPTION LIKE 'clustered%'
AND INDEX_NAME = (SELECT b.name AS ConstraintName
FROM sys.sysconstraints AS a
JOIN sys.key_constraints AS b
ON a.constid = b.object_id
AND a.id = b.parent_object_id
WHERE a.id = Object_id(@TABLE)
AND a.status = 2593))RES
WHERE INDEX_KEYS = @IDENT_COLUMN
OR INDEX_KEYS LIKE '%' + @IDENT_COLUMN + '%' --复合主键
IF @CONSTRAINT_NAME IS NULL
BEGIN
RETURN;
END
IF @INDEX_KEYS IS NULL
BEGIN
RETURN;
END
/*
处理 自增字段存在多个约束或索引中的情况。
先获取包含自增字段的索引索引，然后将这些索引写到 dba_maintenance.dbo.tmp_del_index表中。
最后将这些索引先删除。当重名完自增字段 后再创建这些索引。
*/
BEGIN TRANSACTION TRA_NAME
---将包含自增字段的 非聚集索引 信息备份写入到 维护库的对应表中
INSERT INTO DBA_Maintenance.dbo.tmp_del_index
SELECT Db_name(),
@TABLE,
INDEX_NAME,
INDEX_KEYS,
INDEX_INCLUDE
FROM #ALL_INDEX
WHERE INDEX_DESCRIPTION LIKE '%nonclustered%'
SET @ERROR=@@ERROR
---根据维护库中的备份索引属性信息 删除表上对应的包含自增字段的索引
SELECT @COUNT = Count(*)
FROM DBA_Maintenance.dbo.tmp_del_index
WHERE DBNAME = Db_name()
AND TABLENAME = @TABLE
WHILE @I <= @COUNT
BEGIN
SELECT @tmp_index_name = index_name
FROM (SELECT Row_number()
OVER(
ORDER BY index_name)rn,
index_name
FROM DBA_Maintenance.dbo.tmp_del_index
WHERE DBNAME = Db_name()
AND TABLENAME = @TABLE)tmp
WHERE rn = @I
SET @DEL_INDEX_SQL=N'DROP INDEX ' + @tmp_index_name + ' ON '
+ @TABLE
EXEC (@DEL_INDEX_SQL)
SET @ERROR=@@ERROR+@ERROR
SET @I=@I + 1
END
IF @CONSTRAINT_NAME IS NOT NULL
BEGIN
SET @ALTER_SQL='ALTER TABLE ' + @TABLE
+ ' ADD TmpID INT NOT NULL DEFAULT(0) ;'--增加一个临时字段
SET @UPDATE_SQL= 'UPDATE ' + @TABLE + ' SET TmpID='
+ @IDENT_COLUMN + ''--将自增字段的值赋予临时字段
SET @DROP_CONS_SQL='ALTER TABLE ' + @TABLE + ' DROP CONSTRAINT '
+ @CONSTRAINT_NAME--删除自增字段上的约束
SET @DROP_COLUMN_SQL='ALTER TABLE ' + @TABLE + ' DROP COLUMN '
+ @IDENT_COLUMN--删除自增字段
SET @RENAME_COLUMN_SQL='EXEC SP_RENAME ''' + @TABLE + '.TmpID'','''
+ @IDENT_COLUMN + ''',''COLUMN'''--将临时字段重命名为原自增字段
---依据复合索引还是单自增字段索引来做相应的添回操作
IF Charindex(',', @INDEX_KEYS) >= 1
BEGIN
SET @ADD_CONSTRAINT_SQL='ALTER TABLE ' + @TABLE + ' ADD CONSTRAINT '
+ @CONSTRAINT_NAME
+ ' PRIMARY KEY CLUSTERED ( ' + @INDEX_KEYS
+ ')'---将原来的自增字段的约束添加回。
END
ELSE
BEGIN
SET @ADD_CONSTRAINT_SQL='ALTER TABLE ' + @TABLE
+ ' ADD CONSTRAINT PK_' + @TABLE + '_'
+ @IDENT_COLUMN + ' PRIMARY KEY CLUSTERED ( '
+ @IDENT_COLUMN + ')'---将原来的自增字段的约束添加回。
END
--PRINT @ALTER_SQL
--PRINT @UPDATE_SQL
--PRINT @DROP_CONS_SQL
--PRINT @DROP_COLUMN_SQL
--PRINT @RENAME_COLUMN_SQL
--PRINT @ADD_CONSTRAINT_SQL
EXEC (@ALTER_SQL)
SET @ERROR=@@ERROR+@ERROR
EXEC (@UPDATE_SQL)
SET @ERROR=@@ERROR+@ERROR
EXEC (@DROP_CONS_SQL)
SET @ERROR=@@ERROR+@ERROR
EXEC (@DROP_COLUMN_SQL)
SET @ERROR=@@ERROR+@ERROR
EXEC (@RENAME_COLUMN_SQL)
SET @ERROR=@@ERROR+@ERROR
EXEC (@ADD_CONSTRAINT_SQL)
SET @ERROR=@@ERROR+@ERROR
---- 最后 将被删除的 索引添加回去
SET @I=1
WHILE @I <= @COUNT
BEGIN
SELECT @tmp_index_name = index_name,
@tmp_index_keys = index_keys,
@INDEX_INCLUDE_KEYS=index_include
FROM (SELECT Row_number()
OVER(
ORDER BY index_name)rn,
index_name,
index_keys,
index_include
FROM DBA_Maintenance.dbo.tmp_del_index
WHERE DBNAME = Db_name()
AND TABLENAME = @TABLE)tmp
WHERE rn = @I
IF @index_include_keys IS NOT NULL AND @index_include_keys<>''
BEGIN
SET @CREATE_INDEX_SQL=N'CREATE NONCLUSTERED INDEX '+char(9)+
+ @tmp_index_name +char(9)+ ' ON '+char(9)+@TABLE + '('
+ @tmp_index_keys + ')
INCLUDE(' + @index_include_keys + ')'
END
ELSE
BEGIN
SET @CREATE_INDEX_SQL=N'CREATE NONCLUSTERED INDEX '+char(9)+
+ @tmp_index_name +char(9)+ ' ON '+char(9)+@TABLE + '('
+ @tmp_index_keys + ')'
END
EXEC (@CREATE_INDEX_SQL)
SET @ERROR=@@ERROR+@ERROR
SET @I=@I + 1
END
END
IF @ERROR=0
BEGIN
COMMIT TRANSACTION TRA_NAME
END
IF @ERROR<>0
BEGIN
ROLLBACK TRANSACTION TRA_NAME
END
SET NOCOUNT OFF
END
GO