修改自增属性为非自增
来源：互联网 发布：淘宝旺旺官方买家 编辑：程序博客网 时间：2024/05/01 04:29
该过程是因业务需要需要重构所有表结构--为实现取消表上的自增属性，索引结构保持不变而开发的一套脚本；DECLARE @tablename SYSNAMEDECLARE @i INT=1DECLARE @count INTSELECT @count = Count(*)FROM sys.tablesWHERE type = 'U' AND type_desc = 'USER_TABLE'WHILE @i<=@countBEGIN 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+1ENDGOIF EXISTS(SELECT name FROM sysobjects WHERE NAME = 'Pro_alter_identity' AND type = 'P') DROP PROCEDURE PRO_ALTER_IDENTITYGO-- =============================================-- 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 ENDGO 0 0
- 修改自增属性为非自增
- 修改自增属性
- mysql自增属性
- SQL语句实现取消自增列属性(修改自增列值)
- hibernate 非主键自增
- MYSQL——为现有字段添加自增属性
- mysql为现有字段添加自增属性
- mysql为现有字段添加自增属性
- 获取mysql数据库中的自增主键属性值和非自增主键属性值的方法
- 关于Oracle数据库表中字段修改为自增
- 修改mysql主键的值为自增
- 修改sql自增ID
- mysql 修改自增类型
- mySql 修改字段自增
- postgresql修改自增序列
- mySql 修改字段自增
- SQL表非自增字段自增存储过程
- 非int类型的自增处理
- Mahout(二)：相似性度量
- 前端CSS规范
- 详解spl_autoload_register()函数
- UT-Austin大学在Image search and large-scale retrieval方面的一系列papers
- Winbond Trusted Platform Module 1.2 驱动安装解决办法详解
- 修改自增属性为非自增
- 【python】input
- 设计模式(三)----- 建造者模式(Builder)----(JAVA版)
- Android启动相机拍照并返回图片
- clion 32位xp安装 64位软件
- javascript中有趣的面向对象的思想
- CocoaPods 升级遇到问题
- 信息战（一）——加密程序
- win下同时安装了python2.x和python3.x
