BCPを使ってテーブルをCSVに吐き出すストアドプロシージャー

BCPコマンドを利用してパ指定したテーブルをCSVファイルに吐き出すストアドプロシージャーの作り方です。
ただしBCPコマンドではヘッダー行が出力されないので、SQLに工夫をしています。

BCPコマンドはコマンドプロンプトから実行できるコマンドで、これを利用するには先にxp_cmdshellを使えるようにしておきます。

xp_cmdshellサーバー構成オプション

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

以下のストアドを作ります。

ExportCSV

USE [HOGEHOGE]
GO
/****** Object:  StoredProcedure [dbo].[ExportCSV]    Script Date: 2016/08/25 17:18:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ExportCSV]
    -- Add the parameters for the stored procedure here
    @TableName NVARCHAR(100),
    @FilePath  NVARCHAR(255)
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @raw_sql nvarchar(4000)

    DECLARE @columnHeader VARCHAR(8000)
    SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName


    DECLARE @ColumnList VARCHAR(8000)
    SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST(['+column_name +'] AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName

--  PRINT @ColumnList

    SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM [' + @TableName +']'

--  PRINT @raw_SQL
--  EXECUTE sp_executesql  @raw_sql


    DECLARE @sql VARCHAR(8000);
    SELECT  @sql = 'bcp "'+@raw_sql+ '" queryout '+@FilePath +' -c -t, -T -S' + @@servername + ' -d '+DB_NAME() 

    EXEC master..xp_cmdshell @sql

END

ストアドを呼び出すとCSVファイルが生成されます。

ストアドを呼び出す。

EXEC dbo.ExportCSV 'テーブル名','c:\tmp\test.csv'