如何用T-SQL生成SQL Server表的Oracle兼容DDL脚本

需求：从SQL Server中批量生成对应Oracle的CREATE TABLE语句，解决原脚本中循环逐个选取表和列的问题，同时完善数据类型转换逻辑。

原脚本存在的问题：

• 外层循环无法逐个选取表（缺少筛选条件）
• 内层循环重复生成完整CREATE语句，而非拼接列定义
• 列计数逻辑未关联指定表
• 数据类型转换未考虑长度、精度等细节

完善后的T-SQL脚本 #

-- 临时表存储待处理的表列表
CREATE TABLE #TablesToProcess (
    ID INT IDENTITY(1,1),
    TableName NVARCHAR(256),
    ObjectID INT
)

-- 插入需要处理的表（可添加WHERE条件筛选特定表）
INSERT INTO #TablesToProcess (TableName, ObjectID)
SELECT name, object_id
FROM sys.tables
-- WHERE name LIKE 'XXX%' -- 可选：筛选特定前缀的表

DECLARE @TotalTables INT = (SELECT COUNT(*) FROM #TablesToProcess)
DECLARE @CurrentTableID INT = 1
DECLARE @CurrentTableName NVARCHAR(256)
DECLARE @OracleDDL NVARCHAR(MAX)

WHILE @CurrentTableID <= @TotalTables
BEGIN
    -- 获取当前处理的表
    SELECT @CurrentTableName = TableName
    FROM #TablesToProcess
    WHERE ID = @CurrentTableID

    -- 生成当前表的列定义（含Oracle数据类型转换）
    SELECT @OracleDDL = CONCAT(
        'CREATE TABLE ', UPPER(REPLACE(@CurrentTableName, '_', '')), '(',
        STRING_AGG(
            CONCAT(
                UPPER(REPLACE(c.name, '_', '')), ' ',
                CASE 
                    WHEN t.name = 'MONEY' THEN 'NUMBER(19,4)'
                    WHEN t.name = 'REAL' THEN 'FLOAT(23)'
                    WHEN t.name = 'FLOAT' THEN 'FLOAT(49)'
                    WHEN t.name IN ('NVARCHAR', 'VARCHAR') THEN CONCAT('VARCHAR2(', c.max_length, ')')
                    WHEN t.name IN ('NCHAR', 'CHAR') THEN CONCAT('CHAR(', c.max_length, ')')
                    WHEN t.name = 'INT' THEN 'INTEGER'
                    WHEN t.name = 'BIT' THEN 'NUMBER(1)'
                    WHEN t.name = 'DATETIME' THEN 'DATE'
                    WHEN t.name = 'DECIMAL' THEN CONCAT('NUMBER(', c.precision, ',', c.scale, ')')
                    ELSE UPPER(t.name)
                END
            ),
            ', '
        ),
        ');'
    )
    FROM sys.columns c
    JOIN sys.tables tab ON c.object_id = tab.object_id
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE tab.name = @CurrentTableName

    -- 输出生成的DDL
    PRINT @OracleDDL

    -- 处理下一张表
    SET @CurrentTableID = @CurrentTableID + 1
END

-- 清理临时表
DROP TABLE #TablesToProcess

关键改进说明 #

• 临时表管理待处理表：通过带自增ID的临时表，实现逐个遍历表的逻辑，解决原脚本无筛选条件的问题
• STRING_AGG拼接列定义：用SQL Server 2017+支持的STRING_AGG函数高效拼接列，替代低效的列循环，自动处理逗号分隔
• 完善数据类型映射：补充了更多常用类型的转换（如INT→INTEGER、BIT→NUMBER(1)等），并处理了变长类型的长度参数
• 灵活筛选表：可通过临时表插入时的WHERE子句，筛选特定表（如指定前缀、Schema等）
• 避免重复查询：每次循环仅处理单张表，关联查询时限定表名，提升效率

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