文件作用

说明如何在 Windows 和 Linux 上创建并执行存储过程 dbo.usp_ExportAllTables。不传表名时导出全部用户表；传入 @TableName 时只导出这一张表的结构和数据。

对应脚本：docs/sql/usp_ExportAllTables.sql。

导出用 bcp。不要用 sqlcmd 加 -y 0 导出：这个参数会把 nvarchar(max) 的显示宽度变成 0，生成的文件是空的。-h、-W 也不能和 -y 同时使用。

生成的脚本会先删除再重建这些用户表，只能在空库或允许清空的库中回放。需要 SQL Server 2017 及以上。

下面命令里的服务器、端口、数据库名、账号、密码、输出路径都要换成实际值。

先创建存储过程

在要导出的业务库中执行 usp_ExportAllTables.sql，只需执行一次。过程有改动时再执行一次。

Windows

用 SSMS 打开 usp_ExportAllTables.sql，在工具栏里选中业务库，按 F5。

也可以在 cmd 中执行：

bat
sqlcmd -S localhost,1433 -U 用户 -P 密码 -d 数据库名 -C -i D:\bak\usp_ExportAllTables.sql

Windows 身份验证把 -U 用户 -P 密码 换成 -E。命名实例写成 -S localhost\SQLEXPRESS。sqlcmd 18 连接时加上 -C，用来信任服务器证书。

Linux

bash
/opt/mssql-tools18/bin/sqlcmd -S localhost,5708 -U SA -P '密码' -d 数据库名 -C -i /path/usp_ExportAllTables.sql

没有 mssql-tools18 时，把程序换成 /opt/mssql-tools/bin/sqlcmd，并去掉 -C。

只导出一张表

@TableName 是表名。不传 @SchemaName 时按 dbo 查找。也可以把架构写在表名里。

Windows：

bat
bcp "EXEC dbo.usp_ExportAllTables @TableName = N'表名'" queryout D:\bak\表名.sql -S localhost,1433 -d 数据库名 -U 用户 -P 密码 -c -C 65001 -r "\n" -u

指定架构：

bat
bcp "EXEC dbo.usp_ExportAllTables @SchemaName = N'架构名', @TableName = N'表名'" queryout D:\bak\表名.sql -S localhost,1433 -d 数据库名 -U 用户 -P 密码 -c -C 65001 -r "\n" -u

Linux：

bash
/opt/mssql-tools18/bin/bcp "EXEC dbo.usp_ExportAllTables @TableName = N'表名'" queryout /var/opt/mssql/bak/表名.sql -S localhost,5708 -d 数据库名 -U SA -P '密码' -c -C 65001 -r '\n' -u

指定架构：

bash
/opt/mssql-tools18/bin/bcp "EXEC dbo.usp_ExportAllTables @SchemaName = N'架构名', @TableName = N'表名'" queryout /var/opt/mssql/bak/表名.sql -S localhost,5708 -d 数据库名 -U SA -P '密码' -c -C 65001 -r '\n' -u

单表脚本仍会先删除再重建这张表。其他表如果有外键指向它，脚本会先去掉这些外键，建完表后再加回去。

导出全部表结构和数据

先建好输出目录。命令成功后，文件里就是完整脚本，不需要再删表头。

Windows

SQL 账号，在 cmd 中执行：

bat
bcp "EXEC dbo.usp_ExportAllTables" queryout D:\bak\all_tables.sql -S localhost,1433 -d 数据库名 -U 用户 -P 密码 -c -C 65001 -r "\n" -u

Windows 身份验证：

bat
bcp "EXEC dbo.usp_ExportAllTables" queryout D:\bak\all_tables.sql -S localhost,1433 -d 数据库名 -T -c -C 65001 -r "\n" -u

bcp 不在 PATH 里时，使用 SQL Server 自带的程序，例如：

bat
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe" "EXEC dbo.usp_ExportAllTables" queryout D:\bak\all_tables.sql -S localhost,1433 -d 数据库名 -U 用户 -P 密码 -c -C 65001 -r "\n" -u

旧版 bcp 不认识 -u 时，把 -u 去掉。

Linux

bash
/opt/mssql-tools18/bin/bcp "EXEC dbo.usp_ExportAllTables" queryout /var/opt/mssql/bak/all_tables.sql -S localhost,5708 -d 数据库名 -U SA -P '密码' -c -C 65001 -r '\n' -u

没有 mssql-tools18 时：

bash
/opt/mssql-tools/bin/bcp "EXEC dbo.usp_ExportAllTables" queryout /var/opt/mssql/bak/all_tables.sql -S localhost,5708 -d 数据库名 -U SA -P '密码' -c -C 65001 -r '\n'

参数含义

参数	作用
`-S`	SQL Server 地址。`localhost,端口` 或 `localhost\实例名`
`-d`	要导出的业务库，存储过程必须已经建在这个库里
`-U` `-P`	SQL 账号和密码
`-T`	Windows 上的 Windows 身份验证。只在 Windows 的 `bcp` 里使用
`-E`	Windows 上的 Windows 身份验证。只在 `sqlcmd` 创建过程时使用
`-c`	按字符写出，保留中文和完整的 `INSERT` 文本
`-C 65001`	在 `bcp` 里表示 UTF-8。这和 `sqlcmd` 的 `-C` 不是同一个意思
`-u`	`bcp` 18 信任服务器证书
`-r '\n'`	每一行脚本后面换行
`queryout`	把查询结果写到文件，而不是导出一张物理表

导出文件：

Windows：D:\bak\all_tables.sql
Linux：/var/opt/mssql/bak/all_tables.sql
存储过程usp_ExportAllTables.sql

sql
-- =============================================================================
-- 文件作用：在当前 SQL Server 数据库中创建存储过程 dbo.usp_ExportAllTables。
--           不传表名时，导出当前库全部用户表的结构和数据。
--           传入 @TableName 时，只导出这一张表的结构和数据。
--
-- 导出内容：
--   列定义（类型、长度、精度、是否为空、标识列、默认值、计算列、非库默认排序规则）
--   主键、唯一约束、普通索引（含包含列和筛选条件）、CHECK、外键
--   每张表的全部数据行（一条 INSERT 一行）
--   标识列当前值（DBCC CHECKIDENT RESEED）
--
-- 不导出：视图、过程、函数、触发器、权限、程序集、全文索引、分区方案。
--         XML 索引和空间索引只写注释。内存优化表按普通表导出结构和数据。
--         sql_variant 按字符串字面量导出，原变体类型不会保留。
--
-- 要求：SQL Server 2017 及以上（使用 STRING_AGG、CREATE OR ALTER）。
--
-- 用法：先在业务库执行本文件创建过程，再用 bcp 导出。
--       EXEC dbo.usp_ExportAllTables;                              -- 全部用户表
--       EXEC dbo.usp_ExportAllTables @TableName = N'Orders';       -- dbo.Orders
--       EXEC dbo.usp_ExportAllTables @SchemaName = N'sales', @TableName = N'Orders';
--       不要用 sqlcmd -y 0 导出，该参数会把 nvarchar(max) 写成空内容。
--       Windows 和 Linux 的完整命令见同目录《usp_ExportAllTables运行说明.md》。
--
-- 警告：生成的脚本会先删除再重建这些用户表。只能在空库或允许清空的库中回放。
-- =============================================================================

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.usp_ExportAllTables
    @SchemaName sysname = NULL, -- 架构名。只传表名时按 dbo 查找。表名写成 schema.table 时可以从中拆出
    @TableName sysname = NULL   -- 要导出的表。NULL 或空字符串表示导出全部用户表
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    -- 去掉空格和方括号，这样 [dbo].[Orders] 和 dbo.Orders 都能识别。
    SET @SchemaName = NULLIF(LTRIM(RTRIM(REPLACE(REPLACE(@SchemaName, N'[', N''), N']', N''))), N'');
    SET @TableName = NULLIF(LTRIM(RTRIM(REPLACE(REPLACE(@TableName, N'[', N''), N']', N''))), N'');

    -- 记住调用时是否指定了表。后面拆名字失败时不能退回去导出全部表。
    DECLARE @exportOne bit = CASE WHEN @TableName IS NOT NULL THEN 1 ELSE 0 END;

    -- PARSENAME 从右边取段：第 1 段是表名，第 2 段是架构名。
    -- 已经单独传入架构名时，不再用表名里的架构覆盖它。
    -- 不接受数据库名.架构名.表名，避免把库名误当成架构名。
    IF @TableName IS NOT NULL AND PARSENAME(@TableName, 3) IS NOT NULL
    BEGIN
        RAISERROR(N'表名只支持 表名 或 架构名.表名，不要带数据库名。', 16, 1);
        RETURN;
    END

    IF @TableName IS NOT NULL AND CHARINDEX(N'.', @TableName) > 0
    BEGIN
        IF @SchemaName IS NULL
            SET @SchemaName = PARSENAME(@TableName, 2);
        SET @TableName = PARSENAME(@TableName, 1);
    END

    -- 指定了表名，但拆出来是空的，说明写法无法识别。
    IF @exportOne = 1 AND @TableName IS NULL
    BEGIN
        RAISERROR(N'表名无法识别。请使用表名，或架构名.表名。', 16, 1);
        RETURN;
    END

    -- 只给了表名时，默认到 dbo 架构查找。
    IF @TableName IS NOT NULL AND @SchemaName IS NULL
        SET @SchemaName = N'dbo';

    -- 数据库默认排序规则。列上的排序规则和它不同时，建表语句才写出 COLLATE。
    DECLARE @dbCollation nvarchar(128) = CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS nvarchar(128));
    DECLARE @failCount int = 0;

    -- 单引号字符。后面用它拼“生成出来的 SQL”，避免在源码里手写多层引号。
    DECLARE @q nvarchar(1) = N'''';
    -- 生成代码里的 N'NULL'，求值结果是不带引号的 NULL 单词。
    DECLARE @nullWord nvarchar(20) = N'N' + @q + N'NULL' + @q;
    -- 生成代码里的 N'N'''，用来开始一个 Unicode 字符串字面量。
    DECLARE @strPrefix nvarchar(20) = N'N' + @q + N'N' + REPLICATE(@q, 3);
    -- 生成代码里的 N''''，用来结束一个字符串字面量。
    DECLARE @strSuffix nvarchar(20) = N'N' + REPLICATE(@q, 4);
    -- REPLACE 的查找参数，生成代码为 N''''，运行时是一个单引号。
    DECLARE @searchLit nvarchar(20) = N'N' + REPLICATE(@q, 4);
    -- REPLACE 的替换参数，生成代码为 N''''''，运行时是两个单引号。
    DECLARE @replaceLit nvarchar(20) = N'N' + REPLICATE(@q, 6);
    -- 把回车、换行从字面量里拆出去，保证每一条脚本都是物理上的一行，sqlcmd 才不会把一行 INSERT 拆断。
    DECLARE @crLit nvarchar(40) = N'N' + REPLICATE(@q, 3) + N'+NCHAR(13)+N' + REPLICATE(@q, 3);
    DECLARE @lfLit nvarchar(40) = N'N' + REPLICATE(@q, 3) + N'+NCHAR(10)+N' + REPLICATE(@q, 3);

    IF OBJECT_ID(N'tempdb..#ExportScript') IS NOT NULL
        DROP TABLE #ExportScript;

    -- 最终脚本。SeqNo 只用来保证顺序，返回给客户端时不输出这一列。
    CREATE TABLE #ExportScript
    (
        SeqNo int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        ScriptLine nvarchar(max) NOT NULL
    );

    IF OBJECT_ID(N'tempdb..#Tables') IS NOT NULL
        DROP TABLE #Tables;

    -- 本次要导出的用户表。外部表没有本地完整数据，不进入清单。
    CREATE TABLE #Tables
    (
        rn int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        schema_name sysname NOT NULL,
        table_name sysname NOT NULL,
        object_id int NOT NULL,
        temporal_type tinyint NOT NULL,
        is_memory_optimized bit NOT NULL
    );

    IF OBJECT_ID(N'tempdb..#ColDefs') IS NOT NULL
        DROP TABLE #ColDefs;

    -- 单张表的列定义。literal_expr 为空表示该列不参与 INSERT（计算列、rowversion）。
    CREATE TABLE #ColDefs
    (
        column_id int NOT NULL PRIMARY KEY,
        col_def nvarchar(max) NOT NULL,
        literal_expr nvarchar(max) NULL,
        is_identity bit NOT NULL
    );

    INSERT INTO #Tables (schema_name, table_name, object_id, temporal_type, is_memory_optimized)
    SELECT s.name, t.name, t.object_id, t.temporal_type, t.is_memory_optimized
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE t.is_ms_shipped = 0
      AND t.is_external = 0
      -- 传入表名时只收这一张；没传表名时 @TableName 为 NULL，条件恒成立。
      AND (@TableName IS NULL OR (s.name = @SchemaName AND t.name = @TableName))
    ORDER BY s.name, t.name;

    INSERT INTO #ExportScript (ScriptLine)
    VALUES
        (N'-- 由存储过程 dbo.usp_ExportAllTables 导出'),
        (N'-- 源数据库: ' + DB_NAME()),
        (N'-- 导出范围: ' + CASE WHEN @TableName IS NULL THEN N'全部用户表' ELSE QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) END),
        (N'-- 导出时间: ' + CONVERT(nvarchar(19), GETDATE(), 120)),
        (N'-- 警告: 本脚本会删除并重建所列出的用户表，只能在空库或允许清空的库中回放。'),
        (N'SET NOCOUNT ON;'),
        (N'SET XACT_ABORT ON;'),
        (N'SET ANSI_NULLS ON;'),
        (N'SET QUOTED_IDENTIFIER ON;'),
        (N'GO'),
        (N'');

    -- 指定了表但没找到时直接失败，避免生成一段空脚本。
    IF @TableName IS NOT NULL AND NOT EXISTS (SELECT 1 FROM #Tables)
    BEGIN
        RAISERROR(N'当前数据库中找不到用户表 %s.%s。', 16, 1, @SchemaName, @TableName);
        RETURN;
    END

    -- 没有用户表时直接把说明返回，避免生成一段只会删库的空脚本。
    IF NOT EXISTS (SELECT 1 FROM #Tables)
    BEGIN
        INSERT INTO #ExportScript (ScriptLine)
        VALUES (N'-- 当前数据库没有可导出的用户表。');

        SELECT ScriptLine
        FROM #ExportScript
        ORDER BY SeqNo;
        RETURN;
    END

    -- 非 dbo 架构。CREATE SCHEMA 放在 EXEC 里，这样不必单独占一个批次。
    INSERT INTO #ExportScript (ScriptLine)
    SELECT N'IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'''
           + REPLACE(s.schema_name, N'''', N'''''')
           + N''') EXEC(N''CREATE SCHEMA '
           + QUOTENAME(s.schema_name)
           + N''');'
    FROM
    (
        SELECT DISTINCT schema_name
        FROM #Tables
        WHERE schema_name <> N'dbo'
    ) AS s;

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');

    -- 先删外键，后面才能删除表。
    -- 单表导出时，还要删掉其他表指向这张表的外键，否则回放时 DROP TABLE 会失败；后面会按同样范围把外键加回去。
    INSERT INTO #ExportScript (ScriptLine)
    SELECT N'IF OBJECT_ID(N'''
           + REPLACE(QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(fk.name), N'''', N'''''')
           + N''', N''F'') IS NOT NULL ALTER TABLE '
           + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name)
           + N' DROP CONSTRAINT ' + QUOTENAME(fk.name) + N';'
    FROM sys.foreign_keys AS fk
    INNER JOIN sys.tables AS t ON t.object_id = fk.parent_object_id
    WHERE EXISTS
    (
        SELECT 1
        FROM #Tables AS x
        WHERE x.object_id = fk.parent_object_id
           OR x.object_id = fk.referenced_object_id
    );

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');

    INSERT INTO #ExportScript (ScriptLine)
    SELECT N'DROP TABLE IF EXISTS ' + QUOTENAME(schema_name) + N'.' + QUOTENAME(table_name) + N';'
    FROM #Tables
    ORDER BY rn;

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');

    DECLARE @rn int = 1;
    DECLARE @maxRn int = (SELECT MAX(rn) FROM #Tables);
    DECLARE @curSchema sysname;
    DECLARE @curTable sysname;
    DECLARE @objectId int;
    DECLARE @temporalType tinyint;
    DECLARE @isMemoryOptimized bit;
    DECLARE @fullName nvarchar(512);
    DECLARE @colBlock nvarchar(max);
    DECLARE @pk nvarchar(max);
    DECLARE @uniqueConstraints nvarchar(max);
    DECLARE @ddl nvarchar(max);
    DECLARE @colList nvarchar(max);
    DECLARE @expr nvarchar(max);
    DECLARE @dataSql nvarchar(max);
    DECLARE @hasIdentity bit;
    DECLARE @err nvarchar(4000);
    -- 标识列重播种用。必须在循环外声明，循环里只赋值。
    -- 写在 WHILE 里面的 DECLARE 只会初始化一次，后面的表会沿用第一张表的探测语句。
    DECLARE @hasRows int;
    DECLARE @identCurrent decimal(38, 0);
    DECLARE @probeSql nvarchar(max);

    -- 逐表生成 CREATE TABLE。表之间没有外键依赖，顺序不影响建表。
    WHILE @rn <= @maxRn
    BEGIN
        SELECT
            @curSchema = schema_name,
            @curTable = table_name,
            @objectId = object_id,
            @temporalType = temporal_type,
            @isMemoryOptimized = is_memory_optimized
        FROM #Tables
        WHERE rn = @rn;

        SET @fullName = QUOTENAME(@curSchema) + N'.' + QUOTENAME(@curTable);
        DELETE FROM #ColDefs;

        IF @temporalType <> 0
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'-- ' + @fullName + N' 原为系统版本表或历史表。这里按普通表导出，不重建 SYSTEM_VERSIONING。');
        END

        IF @isMemoryOptimized = 1
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'-- ' + @fullName + N' 原为内存优化表。这里按普通磁盘表导出结构和数据。');
        END

        INSERT INTO #ColDefs (column_id, col_def, literal_expr, is_identity)
        SELECT
            c.column_id,
            CASE
                -- 计算列没有自己的存储类型，定义直接来自目录视图。
                WHEN cc.column_id IS NOT NULL THEN
                    QUOTENAME(c.name) + N' AS ' + cc.definition COLLATE DATABASE_DEFAULT
                    + CASE WHEN cc.is_persisted = 1 THEN N' PERSISTED' ELSE N'' END
                    + CASE WHEN c.is_nullable = 0 AND cc.is_persisted = 1 THEN N' NOT NULL' ELSE N'' END
                ELSE
                    QUOTENAME(c.name) + N' ' +
                    CASE bt.name
                        WHEN N'varchar' THEN N'varchar(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(nvarchar(10), c.max_length) END + N')'
                        WHEN N'char' THEN N'char(' + CONVERT(nvarchar(10), c.max_length) + N')'
                        WHEN N'nvarchar' THEN N'nvarchar(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(nvarchar(10), c.max_length / 2) END + N')'
                        WHEN N'nchar' THEN N'nchar(' + CONVERT(nvarchar(10), c.max_length / 2) + N')'
                        WHEN N'varbinary' THEN N'varbinary(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(nvarchar(10), c.max_length) END + N')'
                        WHEN N'binary' THEN N'binary(' + CONVERT(nvarchar(10), c.max_length) + N')'
                        WHEN N'decimal' THEN N'decimal(' + CONVERT(nvarchar(10), c.precision) + N',' + CONVERT(nvarchar(10), c.scale) + N')'
                        WHEN N'numeric' THEN N'numeric(' + CONVERT(nvarchar(10), c.precision) + N',' + CONVERT(nvarchar(10), c.scale) + N')'
                        WHEN N'datetime2' THEN N'datetime2(' + CONVERT(nvarchar(10), c.scale) + N')'
                        WHEN N'datetimeoffset' THEN N'datetimeoffset(' + CONVERT(nvarchar(10), c.scale) + N')'
                        WHEN N'time' THEN N'time(' + CONVERT(nvarchar(10), c.scale) + N')'
                        WHEN N'float' THEN CASE WHEN c.precision = 53 THEN N'float' ELSE N'float(' + CONVERT(nvarchar(10), c.precision) + N')' END
                        ELSE bt.name
                    END
                    + CASE
                        WHEN ic.column_id IS NOT NULL THEN
                            N' IDENTITY('
                            + CONVERT(nvarchar(40), CONVERT(decimal(38, 0), ic.seed_value))
                            + N','
                            + CONVERT(nvarchar(40), CONVERT(decimal(38, 0), ic.increment_value))
                            + N')'
                        ELSE N''
                      END
                    + CASE
                        WHEN c.collation_name IS NOT NULL AND c.collation_name <> @dbCollation THEN N' COLLATE ' + c.collation_name
                        ELSE N''
                      END
                    + CASE WHEN c.is_rowguidcol = 1 THEN N' ROWGUIDCOL' ELSE N'' END
                    + CASE WHEN c.is_sparse = 1 THEN N' SPARSE' ELSE N'' END
                    + CASE WHEN c.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END
                    + CASE
                        WHEN dc.object_id IS NOT NULL THEN N' CONSTRAINT ' + QUOTENAME(dc.name) + N' DEFAULT ' + dc.definition COLLATE DATABASE_DEFAULT
                        ELSE N''
                      END
            END AS col_def,
            CASE
                -- rowversion 由引擎生成，计算列由表达式生成，都不能出现在 INSERT 列清单里。
                WHEN cc.column_id IS NOT NULL OR bt.name IN (N'timestamp', N'rowversion') THEN NULL
                WHEN bt.name IN (N'varchar', N'char', N'nvarchar', N'nchar', N'text', N'ntext', N'xml', N'sysname', N'sql_variant') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strPrefix
                    + N' + REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'), '
                    + @searchLit + N', ' + @replaceLit
                    + N'), NCHAR(13), ' + @crLit
                    + N'), NCHAR(10), ' + @lfLit
                    + N') + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'date') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(10), ' + QUOTENAME(c.name) + N', 23) + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'datetime', N'smalldatetime') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(23), ' + QUOTENAME(c.name) + N', 121) + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'datetime2', N'datetimeoffset') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(40), ' + QUOTENAME(c.name) + N', 127) + ' + @strSuffix + N' END'
                WHEN bt.name = N'time' THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(30), ' + QUOTENAME(c.name) + N') + ' + @strSuffix + N' END'
                WHEN bt.name = N'uniqueidentifier' THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(36), ' + QUOTENAME(c.name) + N') + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'money', N'smallmoney', N'float', N'real') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE CONVERT(nvarchar(80), ' + QUOTENAME(c.name) + N', 2) END'
                WHEN bt.name IN (N'binary', N'varbinary', N'image') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N', 1) END'
                WHEN bt.name IN (N'geography', N'geometry') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE N' + @q + bt.name COLLATE DATABASE_DEFAULT + N'::STGeomFromWKB(' + @q
                    + N' + CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'.STAsBinary(), 1) + N' + @q + N',' + @q
                    + N' + CONVERT(nvarchar(11), ' + QUOTENAME(c.name) + N'.STSrid) + N' + @q + N')' + @q + N' END'
                WHEN bt.name = N'hierarchyid' THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE N' + @q + N'hierarchyid::Parse(N' + REPLICATE(@q, 3)
                    + N' + REPLACE(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'.ToString()), '
                    + @searchLit + N', ' + @replaceLit
                    + N') + N' + REPLICATE(@q, 2) + N')' + @q + N' END'
                ELSE
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N') END'
            END AS literal_expr,
            CASE WHEN ic.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_identity
        FROM sys.columns AS c
        INNER JOIN sys.types AS bt ON bt.user_type_id = c.system_type_id
        LEFT JOIN sys.identity_columns AS ic
            ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        LEFT JOIN sys.computed_columns AS cc
            ON cc.object_id = c.object_id AND cc.column_id = c.column_id
        LEFT JOIN sys.default_constraints AS dc
            ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE c.object_id = @objectId;

        SET @colBlock = NULL;
        -- 临时表列用 tempdb 排序规则，分隔符用当前库排序规则，两边都转到当前库再拼接。
        SELECT @colBlock = STRING_AGG(CAST(N'    ' + col_def COLLATE DATABASE_DEFAULT AS nvarchar(max)), (N',' + NCHAR(10)) COLLATE DATABASE_DEFAULT)
                           WITHIN GROUP (ORDER BY column_id)
        FROM #ColDefs;

        SET @pk = NULL;
        -- type_desc 固定是 Latin1_General，和当前库排序规则不同，不转换的话 STRING_AGG 会报 4191。
        SELECT @pk = N'CONSTRAINT ' + QUOTENAME(i.name) + N' PRIMARY KEY ' + i.type_desc COLLATE DATABASE_DEFAULT + N' (' + cols.list + N')'
        FROM sys.indexes AS i
        CROSS APPLY
        (
            SELECT STRING_AGG(
                       CAST(QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS nvarchar(max)),
                       N', ')
                   WITHIN GROUP (ORDER BY ic.key_ordinal)
            FROM sys.index_columns AS ic
            INNER JOIN sys.columns AS c
                ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id
              AND ic.index_id = i.index_id
              AND ic.is_included_column = 0
              AND ic.key_ordinal > 0
        ) AS cols(list)
        WHERE i.object_id = @objectId
          AND i.is_primary_key = 1
          AND i.type_desc IN (N'CLUSTERED', N'NONCLUSTERED');

        SET @uniqueConstraints = NULL;
        SELECT @uniqueConstraints = STRING_AGG(
                   CAST(N',' + NCHAR(10) + N'    CONSTRAINT ' + QUOTENAME(i.name) + N' UNIQUE ' + i.type_desc COLLATE DATABASE_DEFAULT + N' (' + cols.list + N')' AS nvarchar(max)),
                   N'')
               WITHIN GROUP (ORDER BY i.index_id)
        FROM sys.indexes AS i
        CROSS APPLY
        (
            SELECT STRING_AGG(
                       CAST(QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS nvarchar(max)),
                       N', ')
                   WITHIN GROUP (ORDER BY ic.key_ordinal)
            FROM sys.index_columns AS ic
            INNER JOIN sys.columns AS c
                ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id
              AND ic.index_id = i.index_id
              AND ic.is_included_column = 0
              AND ic.key_ordinal > 0
        ) AS cols(list)
        WHERE i.object_id = @objectId
          AND i.is_unique_constraint = 1
          AND i.type_desc IN (N'CLUSTERED', N'NONCLUSTERED');

        SET @ddl = N'CREATE TABLE ' + @fullName + N' (' + NCHAR(10) + @colBlock
                   + CASE WHEN @pk IS NOT NULL THEN N',' + NCHAR(10) + N'    ' + @pk ELSE N'' END
                   + ISNULL(@uniqueConstraints, N'')
                   + NCHAR(10) + N');';

        INSERT INTO #ExportScript (ScriptLine) VALUES (@ddl);
        INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');

        SET @rn += 1;
    END

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');

    SET @rn = 1;
    -- 逐表导出数据。一张表失败只记录注释，继续后面的表。
    WHILE @rn <= @maxRn
    BEGIN
        SELECT
            @curSchema = schema_name,
            @curTable = table_name,
            @objectId = object_id
        FROM #Tables
        WHERE rn = @rn;

        SET @fullName = QUOTENAME(@curSchema) + N'.' + QUOTENAME(@curTable);

        DELETE FROM #ColDefs;

        INSERT INTO #ColDefs (column_id, col_def, literal_expr, is_identity)
        SELECT
            c.column_id,
            N'',
            CASE
                WHEN cc.column_id IS NOT NULL OR bt.name IN (N'timestamp', N'rowversion') THEN NULL
                WHEN bt.name IN (N'varchar', N'char', N'nvarchar', N'nchar', N'text', N'ntext', N'xml', N'sysname', N'sql_variant') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strPrefix
                    + N' + REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'), '
                    + @searchLit + N', ' + @replaceLit
                    + N'), NCHAR(13), ' + @crLit
                    + N'), NCHAR(10), ' + @lfLit
                    + N') + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'date') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(10), ' + QUOTENAME(c.name) + N', 23) + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'datetime', N'smalldatetime') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(23), ' + QUOTENAME(c.name) + N', 121) + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'datetime2', N'datetimeoffset') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(40), ' + QUOTENAME(c.name) + N', 127) + ' + @strSuffix + N' END'
                WHEN bt.name = N'time' THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(30), ' + QUOTENAME(c.name) + N') + ' + @strSuffix + N' END'
                WHEN bt.name = N'uniqueidentifier' THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE ' + @strSuffix + N' + CONVERT(nvarchar(36), ' + QUOTENAME(c.name) + N') + ' + @strSuffix + N' END'
                WHEN bt.name IN (N'money', N'smallmoney', N'float', N'real') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE CONVERT(nvarchar(80), ' + QUOTENAME(c.name) + N', 2) END'
                WHEN bt.name IN (N'binary', N'varbinary', N'image') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N', 1) END'
                WHEN bt.name IN (N'geography', N'geometry') THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE N' + @q + bt.name COLLATE DATABASE_DEFAULT + N'::STGeomFromWKB(' + @q
                    + N' + CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'.STAsBinary(), 1) + N' + @q + N',' + @q
                    + N' + CONVERT(nvarchar(11), ' + QUOTENAME(c.name) + N'.STSrid) + N' + @q + N')' + @q + N' END'
                WHEN bt.name = N'hierarchyid' THEN
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE N' + @q + N'hierarchyid::Parse(N' + REPLICATE(@q, 3)
                    + N' + REPLACE(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'.ToString()), '
                    + @searchLit + N', ' + @replaceLit
                    + N') + N' + REPLICATE(@q, 2) + N')' + @q + N' END'
                ELSE
                    N'CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN ' + @nullWord
                    + N' ELSE CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N') END'
            END,
            CASE WHEN ic.column_id IS NOT NULL THEN 1 ELSE 0 END
        FROM sys.columns AS c
        INNER JOIN sys.types AS bt ON bt.user_type_id = c.system_type_id
        LEFT JOIN sys.identity_columns AS ic
            ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        LEFT JOIN sys.computed_columns AS cc
            ON cc.object_id = c.object_id AND cc.column_id = c.column_id
        WHERE c.object_id = @objectId;

        SET @colList = NULL;
        SET @expr = NULL;
        SELECT
            @colList = STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), N', ') WITHIN GROUP (ORDER BY d.column_id),
            @expr = STRING_AGG(CAST(d.literal_expr COLLATE DATABASE_DEFAULT AS nvarchar(max)), (N' + N'','' + ') COLLATE DATABASE_DEFAULT) WITHIN GROUP (ORDER BY d.column_id)
        FROM #ColDefs AS d
        INNER JOIN sys.columns AS c
            ON c.object_id = @objectId AND c.column_id = d.column_id
        WHERE d.literal_expr IS NOT NULL;

        SET @hasIdentity = CASE WHEN EXISTS (SELECT 1 FROM #ColDefs WHERE is_identity = 1 AND literal_expr IS NOT NULL) THEN 1 ELSE 0 END;

        INSERT INTO #ExportScript (ScriptLine) VALUES (N'-- 数据: ' + @fullName);

        -- 有 sql_variant 列时标明精度损失，避免回放后误以为类型和原来完全一致。
        IF EXISTS
        (
            SELECT 1
            FROM sys.columns AS c
            INNER JOIN sys.types AS bt ON bt.user_type_id = c.system_type_id
            WHERE c.object_id = @objectId
              AND bt.name = N'sql_variant'
        )
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'-- ' + @fullName + N' 含 sql_variant 列，数据按 Unicode 字符串导出。');
        END

        -- 没有可插入列时（整表都是计算列）只保留结构。
        IF @colList IS NULL
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'-- ' + @fullName + N' 没有可插入的列，已跳过数据。');
            INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
            SET @rn += 1;
            CONTINUE;
        END

        IF @hasIdentity = 1
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'SET IDENTITY_INSERT ' + @fullName + N' ON;');
        END

        SET @dataSql =
            N'INSERT INTO #ExportScript(ScriptLine) SELECT N''INSERT INTO '
            + @fullName
            + N' ('
            + @colList
            + N') VALUES ('' + '
            + @expr
            + N' + N'');'' FROM '
            + @fullName
            + N';';

        BEGIN TRY
            EXEC sys.sp_executesql @dataSql;
        END TRY
        BEGIN CATCH
            SET @failCount += 1;
            SET @err = ERROR_MESSAGE();
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'-- 导出数据失败: ' + @fullName + N' : ' + @err);
        END CATCH

        IF @hasIdentity = 1
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'SET IDENTITY_INSERT ' + @fullName + N' OFF;');
        END

        INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
        SET @rn += 1;
    END

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'-- 索引');

    DECLARE @indexId int;
    DECLARE @indexName sysname;
    DECLARE @indexType nvarchar(60);
    DECLARE @indexUnique bit;
    DECLARE @indexFilter nvarchar(max);
    DECLARE @indexDisabled bit;
    DECLARE @keyList nvarchar(max);
    DECLARE @includeList nvarchar(max);
    DECLARE @indexSql nvarchar(max);

    DECLARE index_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT
            t.object_id,
            i.index_id,
            i.name,
            i.type_desc COLLATE DATABASE_DEFAULT,
            i.is_unique,
            i.filter_definition COLLATE DATABASE_DEFAULT,
            i.is_disabled,
            QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
        FROM sys.indexes AS i
        INNER JOIN sys.tables AS t ON t.object_id = i.object_id
        INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
        INNER JOIN #Tables AS x ON x.object_id = t.object_id
        WHERE i.index_id > 0
          AND i.is_primary_key = 0
          AND i.is_unique_constraint = 0
          AND i.is_hypothetical = 0
          AND i.name IS NOT NULL
        ORDER BY s.name, t.name, i.index_id;

    OPEN index_cursor;
    FETCH NEXT FROM index_cursor INTO @objectId, @indexId, @indexName, @indexType, @indexUnique, @indexFilter, @indexDisabled, @fullName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- XML 索引和空间索引依赖主索引与专用语法，这里只留注释，避免生成一段无法执行的语句。
        IF @indexType IN (N'XML', N'SPATIAL')
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'-- 未导出 ' + @indexType + N' 索引 ' + QUOTENAME(@indexName) + N'，表 ' + @fullName);
        END
        ELSE IF @indexType = N'CLUSTERED COLUMNSTORE'
        BEGIN
            INSERT INTO #ExportScript (ScriptLine)
            VALUES (N'CREATE CLUSTERED COLUMNSTORE INDEX ' + QUOTENAME(@indexName) + N' ON ' + @fullName + N';');
        END
        ELSE
        BEGIN
            SET @keyList = NULL;
            SELECT @keyList = STRING_AGG(
                       CAST(QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS nvarchar(max)),
                       N', ')
                   WITHIN GROUP (ORDER BY ic.key_ordinal)
            FROM sys.index_columns AS ic
            INNER JOIN sys.columns AS c
                ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = @objectId
              AND ic.index_id = @indexId
              AND ic.is_included_column = 0
              AND ic.key_ordinal > 0;

            SET @includeList = NULL;
            SELECT @includeList = STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), N', ')
                                  WITHIN GROUP (ORDER BY ic.index_column_id)
            FROM sys.index_columns AS ic
            INNER JOIN sys.columns AS c
                ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = @objectId
              AND ic.index_id = @indexId
              AND ic.is_included_column = 1;

            IF @keyList IS NULL AND @indexType <> N'NONCLUSTERED COLUMNSTORE'
            BEGIN
                INSERT INTO #ExportScript (ScriptLine)
                VALUES (N'-- 未导出没有键列的索引 ' + QUOTENAME(@indexName) + N'，表 ' + @fullName);
            END
            ELSE
            BEGIN
                IF @indexType = N'NONCLUSTERED COLUMNSTORE'
                BEGIN
                    SET @indexSql = N'CREATE NONCLUSTERED COLUMNSTORE INDEX ' + QUOTENAME(@indexName)
                                    + N' ON ' + @fullName + N' (' + ISNULL(@keyList, N'') + N')';
                END
                ELSE
                BEGIN
                    SET @indexSql = N'CREATE '
                                    + CASE WHEN @indexUnique = 1 THEN N'UNIQUE ' ELSE N'' END
                                    + @indexType + N' INDEX ' + QUOTENAME(@indexName)
                                    + N' ON ' + @fullName + N' (' + @keyList + N')'
                                    + CASE WHEN @includeList IS NOT NULL THEN N' INCLUDE (' + @includeList + N')' ELSE N'' END
                                    + CASE WHEN @indexFilter IS NOT NULL THEN N' WHERE ' + @indexFilter ELSE N'' END;
                END

                INSERT INTO #ExportScript (ScriptLine) VALUES (@indexSql + N';');

                IF @indexDisabled = 1
                BEGIN
                    INSERT INTO #ExportScript (ScriptLine)
                    VALUES (N'ALTER INDEX ' + QUOTENAME(@indexName) + N' ON ' + @fullName + N' DISABLE;');
                END
            END
        END

        FETCH NEXT FROM index_cursor INTO @objectId, @indexId, @indexName, @indexType, @indexUnique, @indexFilter, @indexDisabled, @fullName;
    END

    CLOSE index_cursor;
    DEALLOCATE index_cursor;

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'-- CHECK 约束');

    INSERT INTO #ExportScript (ScriptLine)
    SELECT N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name)
           + CASE WHEN ck.is_disabled = 1 OR ck.is_not_trusted = 1 THEN N' WITH NOCHECK ' ELSE N' WITH CHECK ' END
           + N'ADD CONSTRAINT ' + QUOTENAME(ck.name)
           + N' CHECK ' + ck.definition COLLATE DATABASE_DEFAULT + N';'
    FROM sys.check_constraints AS ck
    INNER JOIN sys.tables AS t ON t.object_id = ck.parent_object_id
    INNER JOIN #Tables AS x ON x.object_id = t.object_id;

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'-- 外键');

    INSERT INTO #ExportScript (ScriptLine)
    SELECT N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(pt.schema_id)) + N'.' + QUOTENAME(pt.name)
           + CASE WHEN fk.is_disabled = 1 OR fk.is_not_trusted = 1 THEN N' WITH NOCHECK ' ELSE N' WITH CHECK ' END
           + N'ADD CONSTRAINT ' + QUOTENAME(fk.name)
           + N' FOREIGN KEY (' + parent_cols.cols + N') REFERENCES '
           + QUOTENAME(SCHEMA_NAME(rt.schema_id)) + N'.' + QUOTENAME(rt.name)
           + N' (' + ref_cols.cols + N')'
           + N' ON DELETE ' + CASE fk.delete_referential_action
                                  WHEN 1 THEN N'CASCADE'
                                  WHEN 2 THEN N'SET NULL'
                                  WHEN 3 THEN N'SET DEFAULT'
                                  ELSE N'NO ACTION'
                              END
           + N' ON UPDATE ' + CASE fk.update_referential_action
                                  WHEN 1 THEN N'CASCADE'
                                  WHEN 2 THEN N'SET NULL'
                                  WHEN 3 THEN N'SET DEFAULT'
                                  ELSE N'NO ACTION'
                              END
           + N';'
    FROM sys.foreign_keys AS fk
    INNER JOIN sys.tables AS pt ON pt.object_id = fk.parent_object_id
    INNER JOIN sys.tables AS rt ON rt.object_id = fk.referenced_object_id
    CROSS APPLY
    (
        SELECT STRING_AGG(CAST(QUOTENAME(pc.name) AS nvarchar(max)), N', ')
               WITHIN GROUP (ORDER BY fkc.constraint_column_id)
        FROM sys.foreign_key_columns AS fkc
        INNER JOIN sys.columns AS pc
            ON pc.object_id = fkc.parent_object_id AND pc.column_id = fkc.parent_column_id
        WHERE fkc.constraint_object_id = fk.object_id
    ) AS parent_cols(cols)
    CROSS APPLY
    (
        SELECT STRING_AGG(CAST(QUOTENAME(rc.name) AS nvarchar(max)), N', ')
               WITHIN GROUP (ORDER BY fkc.constraint_column_id)
        FROM sys.foreign_key_columns AS fkc
        INNER JOIN sys.columns AS rc
            ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id
        WHERE fkc.constraint_object_id = fk.object_id
    ) AS ref_cols(cols)
    WHERE EXISTS
    (
        SELECT 1
        FROM #Tables AS x
        WHERE x.object_id = fk.parent_object_id
           OR x.object_id = fk.referenced_object_id
    );

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'');
    INSERT INTO #ExportScript (ScriptLine) VALUES (N'-- 标识列当前值。空表沿用 CREATE TABLE 里的种子，不重新播种。');

    SET @rn = 1;
    WHILE @rn <= @maxRn
    BEGIN
        SELECT
            @curSchema = schema_name,
            @curTable = table_name,
            @objectId = object_id
        FROM #Tables
        WHERE rn = @rn;

        SET @fullName = QUOTENAME(@curSchema) + N'.' + QUOTENAME(@curTable);

        -- 只有真正产生过标识值的表才 RESEED，空表保持原来的种子。
        IF EXISTS (SELECT 1 FROM sys.identity_columns WHERE object_id = @objectId)
        BEGIN
            SET @hasRows = 0;
            SET @identCurrent = NULL;
            -- 每张表单独探测有没有数据行，不能复用上一张表的语句。
            SET @probeSql = N'SELECT @n = CASE WHEN EXISTS (SELECT 1 FROM ' + @fullName + N') THEN 1 ELSE 0 END;';

            EXEC sys.sp_executesql @probeSql, N'@n int OUTPUT', @n = @hasRows OUTPUT;

            IF @hasRows = 1
            BEGIN
                SET @identCurrent = CONVERT(decimal(38, 0), IDENT_CURRENT(@fullName));
                IF @identCurrent IS NOT NULL
                BEGIN
                    INSERT INTO #ExportScript (ScriptLine)
                    VALUES (N'DBCC CHECKIDENT (N''' + REPLACE(@fullName, N'''', N'''''') + N''', RESEED, '
                            + CONVERT(nvarchar(40), @identCurrent) + N');');
                END
            END
        END

        SET @rn += 1;
    END

    INSERT INTO #ExportScript (ScriptLine) VALUES (N'GO');

    SELECT ScriptLine
    FROM #ExportScript
    ORDER BY SeqNo;

    IF @failCount > 0
    BEGIN
        RAISERROR(N'有 %d 张表的数据没有导出，请在结果里搜索“导出数据失败”。', 16, 1, @failCount);
    END
END
GO