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

一括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