一、问题描述

客户是由SQL Server2012数据库迁移到达梦数据库，迁移后在应用测试时发现，包含自增列的表做数据插入（insert）操作时会有如下报错：仅当指定列列表,，且SET IDENTITY INSERT为ON时，才能对自增列赋值。

该报错是因为在达梦数据库中当插入数据并且要指定自增列的值时，必须要通过语句将 IDENTITY_INSERT 设置为 ON ，且插入语句中必须指定表中自增列字段要插入的值。该部分内容详见手册《DM8_SQL语言使用手册.pdf》第5章5.6自增列的使用。如图：

由于需要添加SQL，修改代码，所以客户决定把所有的自增列全部去掉自增。因此就有了需要批量删除模式下所有自增列的需求。

二、具体方法

首先不管用什么方法，都需要用到一条SQL：查询所有包含自增列的表。所以下面就是两种查询SQL：

SQL1

SELECT TAB.NAME AS TABLE_NAME , 
       COL.NAME AS COLUMN_NAME
  FROM SYSOBJECTS TAB , 
       SYSCOLUMNS COL
 WHERE COL.ID = TAB.ID
   AND TAB.TYPE$ = 'SCHOBJ'
   AND TAB.SUBTYPE$ IN ('UTAB')
   AND COL.INFO2 & 0x01 = 1
   AND TAB.SCHID = CURRENT_SCHID;

SQL2

WITH TMP AS 
     ( SELECT SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID) AS TABLE_OWNER,                                -- 模式名
              ST.NAME AS TABLE_NAME,                                                            -- 表名
              SCO.NAME AS COLUMN_NAME,                                                          -- 列名
              SCO.TYPE$ AS COLUMN_TYPE ,                                                        -- 列类型
              IDENT_CURRENT(SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID)||'.'||ST.NAME) AS IDENT_CURRENT, -- 自增列当前值
              IDENT_SEED(SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID)||'.'||ST.NAME) AS IDENT_SEED,       -- 种子值（起始值）
              IDENT_INCR(SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID)||'.'||ST.NAME) AS IDENT_INCREMENT   -- 增量值
         FROM SYSCOLUMNS SCO
         JOIN SYSOBJECTS ST 
           ON SCO.ID = ST.ID 
          AND ST.SUBTYPE$='UTAB'
        WHERE BITAND(SCO.INFO2, 0X0001) = 1
     ORDER BY 1, 
              2
     )
SELECT * FROM TMP WHERE TABLE_OWNER = 'TEST'

方法一：SQL生成SQL

利用上边的查询SQL，在结果集生成删除SQL，再执行结果集的删除SQL。

SELECT 'ALTER TABLE '|| TABLE_NAME ||' DROP IDENTITY;' FROM 
(
WITH TMP AS 
     ( SELECT SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID) AS TABLE_OWNER,                                -- 模式名
              ST.NAME AS TABLE_NAME,                                                            -- 表名
              SCO.NAME AS COLUMN_NAME,                                                          -- 列名
              SCO.TYPE$ AS COLUMN_TYPE ,                                                        -- 列类型
              IDENT_CURRENT(SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID)||'.'||ST.NAME) AS IDENT_CURRENT, -- 自增列当前值
              IDENT_SEED(SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID)||'.'||ST.NAME) AS IDENT_SEED,       -- 种子值（起始值）
              IDENT_INCR(SF_GET_SCHEMA_NAME_BY_ID(ST.SCHID)||'.'||ST.NAME) AS IDENT_INCREMENT   -- 增量值
         FROM SYSCOLUMNS SCO
         JOIN SYSOBJECTS ST 
           ON SCO.ID = ST.ID 
          AND ST.SUBTYPE$='UTAB'
        WHERE BITAND(SCO.INFO2, 0X0001) = 1
     ORDER BY 1, 
              2
     )
SELECT * FROM TMP WHERE TABLE_OWNER = 'TEST'
);

生成的结果集，如图

结果集中的SQL即批量删除SQL：

ALTER TABLE AUTOINCREMENT1 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT2 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT3 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT4 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT5 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT6 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT7 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT8 DROP IDENTITY;
ALTER TABLE AUTOINCREMENT9 DROP IDENTITY;

执行即删除完毕。

方法二：存储过程删除

CREATE OR REPLACE
PROCEDURE "TEST"."DROP_TABLE_AUTO_INCREMENT"
AS
    DECLARE
        V_TABLE_NAME VARCHAR(1000);
        -- 游标，用于遍历查询结果中的表名
        CURSOR C_TABLE IS
            SELECT TAB.NAME AS TABLE_NAME
                   --,COL.NAME AS COLUMN_NAME
              FROM SYSOBJECTS TAB ,
                   SYSCOLUMNS COL
             WHERE COL.ID = TAB.ID
               AND TAB.TYPE$ = 'SCHOBJ'
               AND TAB.SUBTYPE$ IN ('UTAB')
               AND COL.INFO2 
                   & 0x01 = 1
               AND TAB.SCHID = CURRENT_SCHID;
    
    BEGIN
        -- 打开游标
        OPEN C_TABLE;
        LOOP
            -- 提取表名
            FETCH C_TABLE INTO V_TABLE_NAME;
            
            EXIT WHEN C_TABLE%NOTFOUND;
            -- 执行ALTER TABLE语句
            EXECUTE IMMEDIATE 'ALTER TABLE '||V_TABLE_NAME||' DROP IDENTITY;';
        END LOOP;
        -- 关闭游标
        CLOSE C_TABLE;
    END;
/

调用存储过程删除

call "TEST"."DROP_TABLE_AUTO_INCREMENT"();