USE cimforce_xunsheng;
GO
DECLARE @TableName NVARCHAR(128);
DECLARE table_cursor CURSOR FOR
    SELECT name FROM sys.tables WHERE type = 'U'; -- 只处理用户表
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'';
    -- 生成PostgreSQL风格的CREATE TABLE语句
    SELECT @SQL = N'CREATE TABLE "' + SCHEMA_NAME(t.schema_id) + '"."' + t.name + '" (' + CHAR(10)
        + STUFF((
            SELECT ', ' + CHAR(10) 
                + '  "' + c.name + '" ' 
                -- 数据类型转换
                + CASE 
                    WHEN tp.name = 'varchar' AND c.max_length = -1 THEN 'text'
                    WHEN tp.name = 'varchar' THEN 'varchar(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')'
                    WHEN tp.name = 'nvarchar' AND c.max_length = -1 THEN 'text'
                    WHEN tp.name = 'nvarchar' THEN 'varchar(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length/2 AS VARCHAR) END + ')'
                    WHEN tp.name = 'datetime' OR tp.name = 'datetime2' THEN 'timestamp'
                    WHEN tp.name = 'smalldatetime' THEN 'timestamp'
                    WHEN tp.name = 'bit' THEN 'boolean'
                    WHEN tp.name = 'decimal' THEN 'numeric(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
                    WHEN tp.name = 'uniqueidentifier' THEN 'uuid'
                    WHEN tp.name = 'int' AND EXISTS (
                        SELECT 1 FROM sys.identity_columns ic WHERE ic.object_id = t.object_id AND ic.column_id = c.column_id
                    ) THEN 'integer GENERATED ALWAYS AS IDENTITY' -- 自增列
                    ELSE tp.name -- 其他兼容类型（如int, float等）
                END
                -- 非空约束
                + CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
                -- 主键约束（只处理单字段主键）
                + CASE WHEN EXISTS (
                    SELECT 1 FROM sys.indexes i
                    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                    WHERE i.is_primary_key = 1 AND i.object_id = t.object_id AND ic.column_id = c.column_id
                ) THEN ' PRIMARY KEY' ELSE '' END
            FROM sys.columns c
            JOIN sys.types tp ON c.system_type_id = tp.system_type_id
            WHERE c.object_id = t.object_id
            ORDER BY c.column_id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + CHAR(10) + ');' + CHAR(10) + CHAR(10)
    FROM sys.tables t
    WHERE t.name = @TableName;

    PRINT @SQL; -- 输出转换后的表结构
    FETCH NEXT FROM table_cursor INTO @TableName;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
GO
