无法访问客户服务器时如何用SSMS导出未知表数据库的插入脚本

前提：确保你有权限通过本地SSMS v10连接到目标SQL Server实例，且对目标数据库有SELECT权限、系统表查询权限。 #

第一步：查询目标库所有用户表 #

执行以下脚本可以直接获取目标库下所有用户创建的表，无需提前知晓库内结构：

USE [你的目标数据库名称]
GO
SELECT name AS 表名 FROM sys.tables 
WHERE type = 'U' AND is_ms_shipped = 0
ORDER BY name

第二步：创建INSERT语句生成存储过程 #

在目标库执行以下脚本，创建通用的单表插入语句生成存储过程，兼容SSMS v10对应SQL Server 2008版本：

CREATE PROCEDURE dbo.sp_generate_inserts
(
    @table_name varchar(255),
    @where_clause varchar(1000) = NULL
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @columns varchar(max), @values varchar(max), @sql varchar(max), @column_name varchar(255), @data_type int
    
    -- 遍历获取表所有非计算列
    DECLARE col_cursor CURSOR FOR
    SELECT c.name, t.system_type_id
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    WHERE c.object_id = OBJECT_ID(@table_name)
    AND c.is_computed = 0
    ORDER BY c.column_id
    
    SET @columns = ''
    SET @values = ''
    
    OPEN col_cursor
    FETCH NEXT FROM col_cursor INTO @column_name, @data_type
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @columns = @columns + '[' + @column_name + '], '
        -- 适配不同数据类型的转义规则
        SET @values = @values + CASE 
            WHEN @data_type IN (167,175,231,239,35,99) THEN ''''''' + REPLACE(CONVERT(varchar(max),ISNULL([' + @column_name + '],'''')),''''''','''''''''''') + ''''''
            WHEN @data_type IN (58,61) THEN ''''''' + CONVERT(varchar,ISNULL([' + @column_name + '],''1900-01-01''),120) + ''''''
            WHEN @data_type IN (104) THEN 'CASE WHEN ISNULL([' + @column_name + '],0) = 1 THEN ''1'' ELSE ''0'' END'
            ELSE 'ISNULL(CONVERT(varchar(max),[' + @column_name + ']),''NULL'')'
        END + ','' ,'' + '
        FETCH NEXT FROM col_cursor INTO @column_name, @data_type
    END
    CLOSE col_cursor
    DEALLOCATE col_cursor
    
    -- 清理末尾多余字符
    SET @columns = LEFT(@columns, LEN(@columns)-1)
    SET @values = LEFT(@values, LEN(@values)-8)
    
    -- 拼接生成INSERT的查询语句
    SET @sql = 'SELECT ''INSERT INTO ' + @table_name + ' (' + @columns + ') VALUES ('' + ' + @values + ' + '')'' FROM ' + @table_name
    IF @where_clause IS NOT NULL AND @where_clause != ''
        SET @sql = @sql + ' WHERE ' + @where_clause
    
    -- 输出结果到消息面板
    PRINT 'SET IDENTITY_INSERT ' + @table_name + ' ON;'
    EXEC (@sql)
    PRINT 'SET IDENTITY_INSERT ' + @table_name + ' OFF;'
    PRINT 'GO'
END
GO

如果你没有目标库的存储过程创建权限，可以将代码中CREATE PROCEDURE dbo.sp_generate_inserts改为CREATE PROCEDURE #sp_generate_inserts，创建临时存储过程，会话关闭后会自动删除，后续调用时替换为#sp_generate_inserts即可。

第三步：批量生成所有表的INSERT语句 #

执行以下脚本即可遍历所有用户表，自动生成对应INSERT语句，所有输出会展示在SSMS的「消息」面板，直接复制内容保存为.sql文件即可：

USE [你的目标数据库名称]
GO
SET NOCOUNT ON
DECLARE @table_name varchar(255)
DECLARE table_cursor CURSOR FOR
SELECT name FROM sys.tables WHERE type = 'U' AND is_ms_shipped = 0
ORDER BY name

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '/* 表：' + @table_name + ' 插入语句 */'
    EXEC dbo.sp_generate_inserts @table_name
    FETCH NEXT FROM table_cursor INTO @table_name
END

CLOSE table_cursor
DEALLOCATE table_cursor
GO

注意事项 #

• 提前调整SSMS输出限制：依次打开「工具」→「选项」→「查询结果」→「SQL Server」→「以文本格式显示结果」，将「每列最多字符数」调整为最大值8192；如果单条数据长度超过限制，可以选择「将结果保存到文件」避免内容截断
• 自增主键已适配：生成的语句自带SET IDENTITY_INSERT开关，导入时无需额外处理
• 大数据量建议分批生成：如果库内数据量很大，建议按表分批生成，避免SSMS内存溢出
• 生成后先做校验：可先抽取1-2个小表的插入语句测试执行，确认转义规则、数据格式正确
• 生成完成后可执行DROP PROCEDURE dbo.sp_generate_inserts清理创建的存储过程

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