SQLServer
BCP

DB内のテーブルを一括でCSV出力する

More than 3 years have passed since last update.


一括CSV出力.sql

DECLARE @TableName NVARCHAR(31)

DECLARE @Counter INT
DECLARE @ServerName as char(256)
DECLARE @User as char(256)
DECLARE @Pass as char(256)
DECLARE @DBName as char(256)
DECLARE @OUTPass as char(256)
DECLARE @sqlTex as char(1024)

--接続情報・出力先--
set @ServerName = '' --サーバ名
set @User = '' --ユーザ名
set @Pass = '' --パスワード
set @DBName = '' --DB名
set @OUTPass = '' --出力パス

DECLARE TableName CURSOR FOR
SELECT t.name as TABLE_NAME
FROM [DB名].sys.tables t order by TABLE_NAME

SET NOCOUNT ON

--メイン処理
OPEN TableName

FETCH NEXT FROM TableName
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

select @sqlTex = 'bcp "SELECT * FROM ' + @DBName + '.dbo.'+ @TableName + ' '
+ '" queryout "' + @OUTPass + ''+ @TableName + '.csv" -c -t "," -S ' + @ServerName + ' -U '+ @User + ' -P ' + @Pass + ''
EXEC master..xp_cmdshell @sqlTex

FETCH NEXT FROM TableName
INTO @TableName
END

--終了処理
CLOSE TableName
DEALLOCATE TableName

SET NOCOUNT OFF



xp_cmdshell許可.sql

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO