双机热备软件PanguHA图文教程

 -- 设置目标数据库名称
Use B;
DECLARE @DatabaseName NVARCHAR(128) = N'B';

-- 验证数据库是否存在
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
    RAISERROR(N'错误：数据库 "%s" 不存在！', 16, 1, @DatabaseName);
    RETURN;
END;

-- 声明变量
DECLARE @SchemaName    NVARCHAR(128),
        @TableName     NVARCHAR(128),
        @FullTableName  NVARCHAR(255),
        @SqlStatement  NVARCHAR(MAX);

-- 创建游标获取所有用户表
DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.databases db
INNER JOIN 
    sys.tables t ON db.name = @DatabaseName
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    db.name = @DatabaseName 
    AND t.type = 'U';  -- 只获取用户表

-- 开始处理
OPEN tableCursor;

FETCH NEXT FROM tableCursor INTO @SchemaName, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- 构造完整表名和SQL语句
        SET @FullTableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
        SET @SqlStatement = N'USE ' + QUOTENAME(@DatabaseName) + N'; 
        TRUNCATE TABLE ' + @FullTableName;

        -- 执行截断操作
        EXEC sp_executesql @SqlStatement;
        PRINT N'成功截断表: ' + @FullTableName;
    END TRY
    BEGIN CATCH
        PRINT N'错误截断表: ' + @FullTableName + N' | 错误信息: ' + ERROR_MESSAGE();
    END CATCH

    FETCH NEXT FROM tableCursor INTO @SchemaName, @TableName;
END;

-- 清理资源
CLOSE tableCursor;
DEALLOCATE tableCursor;

PRINT N'操作完成！请检查输出信息确认结果。';

DO $$
DECLARE
    table_name TEXT;
BEGIN
    -- 获取所有用户表
    FOR table_name IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'
         
    LOOP
        -- 清空表并重置序列
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(table_name) || ' RESTART IDENTITY CASCADE';
        RAISE NOTICE '已清空表: %', table_name;
    END LOOP;
END 
$$;

DECLARE @DatabaseName NVARCHAR(128) = N'AA-1' -- 替换为你的数据库名称

-- 切换到目标数据库
USE [AA-1] -- 这里也需要替换为实际的数据库名称

DECLARE @TableSchema NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)
DECLARE @Sql NVARCHAR(MAX)
DECLARE @PKName NVARCHAR(128)

-- 创建游标遍历所有用户表
DECLARE table_cursor CURSOR FOR
SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_CATALOG = @DatabaseName

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableSchema, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 检查表是否有主键
    IF NOT EXISTS (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
            ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
        WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
          AND tc.TABLE_SCHEMA = @TableSchema
          AND tc.TABLE_NAME = @TableName
          AND tc.TABLE_CATALOG = @DatabaseName
    )
    BEGIN
        -- 检查表是否有自增列
        IF EXISTS (
            SELECT 1 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = @TableSchema 
              AND TABLE_NAME = @TableName 
              AND TABLE_CATALOG = @DatabaseName
              AND COLUMNPROPERTY(
                  OBJECT_ID(QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName)), 
                  COLUMN_NAME, 
                  'IsIdentity'
              ) = 1
        )
        BEGIN
            -- 获取自增列名
            SELECT @ColumnName = COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = @TableSchema 
              AND TABLE_NAME = @TableName 
              AND TABLE_CATALOG = @DatabaseName
              AND COLUMNPROPERTY(
                  OBJECT_ID(QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName)), 
                  COLUMN_NAME, 
                  'IsIdentity'
              ) = 1
            
            -- 设置自增列为主键
            SET @PKName = 'PK_' + @TableName
            SET @Sql = N'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + 
                      N' ADD CONSTRAINT ' + QUOTENAME(@PKName) + 
                      N' PRIMARY KEY (' + QUOTENAME(@ColumnName) + N')'
            
            PRINT N'为表 ' + @TableSchema + '.' + @TableName + N' 设置自增列为主键: ' + @Sql
            EXEC sp_executesql @Sql
        END
        ELSE
        BEGIN
            -- 创建PanguId自增主键
            SET @PKName = 'PK_' + @TableName
            SET @Sql = N'
            IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE TABLE_SCHEMA = @Schema 
                            AND TABLE_NAME = @Table 
                            AND COLUMN_NAME = ''PanguId'')
            BEGIN
                ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + 
                N' ADD PanguId bigint IDENTITY(1,1) NOT NULL
            END
            
            ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + 
            N' ADD CONSTRAINT ' + QUOTENAME(@PKName) + 
            N' PRIMARY KEY (PanguId)'
            
            PRINT N'为表 ' + @TableSchema + '.' + @TableName + N' 创建PanguId自增主键'
            
            EXEC sp_executesql @Sql, 
                N'@Schema NVARCHAR(128), @Table NVARCHAR(128)', 
                @Schema = @TableSchema, @Table = @TableName
        END
    END
    ELSE
    BEGIN
        PRINT N'表 ' + @TableSchema + '.' + @TableName + N' 已有主键，跳过处理'
    END
    
    FETCH NEXT FROM table_cursor INTO @TableSchema, @TableName
END

CLOSE table_cursor
DEALLOCATE table_cursor