如何在T-SQL中生成含正确列名与数据类型的Oracle建表语句？

源表存储在SQL Server中，使用T-SQL生成Oracle建表DDL时，列数正确，但会重复输出表最后一列的名称和数据类型。例如TABLE_01有6列，却重复输出6次ISKEY INT，错误示例如下：

CREATE TABLE  TABLE_01(
ISKEY   INT
ISKEY   INT
ISKEY   INT
ISKEY   INT
ISKEY   INT
ISKEY   INT
);

用户提供的代码 #

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('TABLE_01')
INSERT INTO @MyList VALUES ('TABLE_02')
INSERT INTO @MyList VALUES ('TABLE_03')
INSERT INTO @MyList VALUES ('TABLE_04')

DECLARE @VALUE VARCHAR(50)

DECLARE @COLNAME VARCHAR(50) = ''
DECLARE @COLTYPE VARCHAR(50) = ''

DECLARE @COLNUM INT = 0
DECLARE @COL_COUNTER INT = 0

DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)

-- Loop for Multiple Tables
WHILE @COUNTER < @MAX
BEGIN
SET @VALUE = (SELECT VALUE FROM
      (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , Value from @MyList) R 
       ORDER BY R.[index] OFFSET @COUNTER 
       ROWS FETCH NEXT 1 ROWS ONLY);

        SELECT  CONCAT('CREATE TABLE  ' , REPLACE(UPPER(@VALUE), '_',''), '(')
        PRINT 'CREATE TABLE  ' + REPLACE(UPPER(@VALUE), '_','') + '('
    
        SET @COLNUM = 0
        SET @COL_COUNTER = 0

        ;WITH numcol AS
        (
        select schema_name(tab.schema_id) as schema_name,
                tab.name as table_name, 
                col.column_id,
                col.name as column_name, 
                t.name as data_type,    
                col.max_length,
                col.precision
         from sys.tables as tab
                inner join sys.columns as col
                on tab.object_id = col.object_id
                left join sys.types as t
                on col.user_type_id = t.user_type_id
                where schema_name(tab.schema_id) = 'dbo' AND tab.name = @VALUE
        )
        SELECT @COLNUM = COUNT(*) OVER (PARTITION BY schema_name, table_name) FROM numcol

        -- Loop for Multiple Columns
        WHILE @COL_COUNTER < @COLNUM
        BEGIN

        SET @COLNAME = ''
        SET @COLTYPE = ''

        SELECT @COLNAME = REPLACE(UPPER(COL.name), '_',''), @COLTYPE = CASE WHEN UPPER(col_type.name) = 'MONEY' THEN '  ' +'    NUMBER(19,4)'
                                                            WHEN UPPER(col_type.name) = 'REAL' THEN '   ' +'    FLOAT(23)'
                                                            WHEN UPPER(col_type.name) = 'FLOAT' THEN '  ' +'    FLOAT(49)'
                                                            WHEN UPPER(col_type.name) = 'NVARCHAR' THEN '   ' +'    NCHAR'
                                                            ELSE '  ' + UPPER(col_type.name)
                                                            END
                                                            
        FROM sys.columns COL
             INNER JOIN sys.tables TAB
             On COL.object_id = TAB.object_id
             left join sys.types as col_type
             on col.user_type_id = col_type.user_type_id
        WHERE OBJECT_NAME(TAB.object_id) = @VALUE
        
        PRINT @COLNAME + @COLTYPE 
        SET @COL_COUNTER = @COL_COUNTER + 1

        END 
        PRINT ');'
SET @COUNTER = @COUNTER + 1

END

问题原因 #

列循环逻辑中，每次查询sys.columns时没有指定具体的列序号，导致每次都会返回当前表的所有列。而SQL Server给变量@COLNAME和@COLTYPE赋值时，只会用查询结果的最后一行覆盖变量，因此每次循环都得到最后一列的信息，最终重复输出多次。

修复后的代码 #

核心修改点：

• 列循环时，根据@COL_COUNTER指定对应序号的列（column_id从1开始，所以要加1）
• 优化系统视图查询逻辑，避免重复查询
• 增加列逗号判断，符合Oracle DDL语法规范（最后一列不能加逗号）

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('TABLE_01')
INSERT INTO @MyList VALUES ('TABLE_02')
INSERT INTO @MyList VALUES ('TABLE_03')
INSERT INTO @MyList VALUES ('TABLE_04')

DECLARE @VALUE VARCHAR(50)
DECLARE @COLNAME VARCHAR(50) = ''
DECLARE @COLTYPE VARCHAR(50) = ''
DECLARE @COLNUM INT = 0
DECLARE @COL_COUNTER INT = 0
DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)

-- 遍历多个表
WHILE @COUNTER < @MAX
BEGIN
    -- 获取当前要处理的表名
    SET @VALUE = (SELECT VALUE FROM
          (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) [index], Value FROM @MyList) R 
           ORDER BY R.[index] OFFSET @COUNTER ROWS FETCH NEXT 1 ROWS ONLY);

    PRINT 'CREATE TABLE  ' + REPLACE(UPPER(@VALUE), '_','') + '('

    SET @COLNUM = 0
    SET @COL_COUNTER = 0

    -- 获取当前表的列总数
    SELECT @COLNUM = COUNT(*) 
    FROM sys.columns COL
    INNER JOIN sys.tables TAB ON COL.object_id = TAB.object_id
    WHERE TAB.name = @VALUE AND SCHEMA_NAME(TAB.schema_id) = 'dbo'

    -- 遍历当前表的所有列
    WHILE @COL_COUNTER < @COLNUM
    BEGIN
        SET @COLNAME = ''
        SET @COLTYPE = ''

        -- 根据列序号获取对应列的信息（column_id从1开始，所以@COL_COUNTER+1）
        SELECT 
            @COLNAME = REPLACE(UPPER(COL.name), '_',''), 
            @COLTYPE = CASE 
                WHEN UPPER(col_type.name) = 'MONEY' THEN '    NUMBER(19,4)'
                WHEN UPPER(col_type.name) = 'REAL' THEN '    FLOAT(23)'
                WHEN UPPER(col_type.name) = 'FLOAT' THEN '    FLOAT(49)'
                WHEN UPPER(col_type.name) = 'NVARCHAR' THEN '    NCHAR'
                ELSE '    ' + UPPER(col_type.name)
            END
        FROM sys.columns COL
        INNER JOIN sys.tables TAB ON COL.object_id = TAB.object_id
        LEFT JOIN sys.types col_type ON COL.user_type_id = col_type.user_type_id
        WHERE TAB.name = @VALUE 
          AND SCHEMA_NAME(TAB.schema_id) = 'dbo'
          AND COL.column_id = @COL_COUNTER + 1 -- 关键：指定列序号

        -- 输出列定义，最后一列不加逗号
        IF @COL_COUNTER < @COLNUM - 1
            PRINT @COLNAME + @COLTYPE + ','
        ELSE
            PRINT @COLNAME + @COLTYPE

        SET @COL_COUNTER = @COL_COUNTER + 1
    END 
    PRINT ');'
    SET @COUNTER = @COUNTER + 1
END

内容的提问来源于stack exchange，提问作者llearner