使用实用程序批量复制将所有表从SQL Server数据库导出到文件中 [英] Exporting all tables into files from SQL Server database using utility bulk copy

问题描述

我想将所有表都bcp到数据库中的文件中:

I want to bcp all tables into files from a database:

SELECT 'EXEC xp_cmdshell ''bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               --database name
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- schema
+  QUOTENAME(name)                         -- table
+ ' out c:\temp\'                          -- output directory
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+  REPLACE(name,' ','')                    -- file name
+ '.txt -T -c'''   -- extension, security
FROM sys.tables

它产生如下语句:

EXEC xp_cmdshell 'bcp [AdventureWorks2012].[Production].[ScrapReason] out c:\temp\Production_ScrapReason.txt -T -c'

EXEC xp_cmdshell 'bcp [AdventureWorks2012].[HumanResources].[Shift] out c:\temp\HumanResources_Shift.txt -T -c'

所以我要遍历以上语句并执行所有语句.该怎么做?

so what I want is to iterate over above statements and execute all them. How to do this?

推荐答案

请注意，这假定所有命令都是唯一的.

Note this assumes all commands are unique.

DECLARE @Commands TABLE(CommandText NVARCHAR(4000));
DECLARE @SQL NVARCHAR(4000);

INSERT INTO @Commands
SELECT 'EXEC xp_cmdshell ''bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               --database name
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- schema
+  QUOTENAME(name)                         -- table
+ ' out c:\temp\'                          -- output directory
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+  REPLACE(name,' ','')                    -- file name
+ '.txt -T -c'''   -- extension, security
FROM sys.tables

WHILE (SELECT COUNT(*) FROM @Commands) > 0
BEGIN --Command Processing
    SET @SQL = (SELECT TOP 1 CommandText FROM @Commands)
    --PRINT (@SQL)
    EXEC (@SQL)
    DELETE FROM @Commands WHERE CommandText = @SQL
END

这篇关于使用实用程序批量复制将所有表从SQL Server数据库导出到文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！