こんにちは。

キャスレーコンサルティングのSI(システム・インテグレーション)部の上條です。

 

今回は、MSSQLServer2014において、ちょっとした小技を紹介したいと思います。

MSSQLServerにはテーブルの内容をファイル出力する機能がありますが、1テーブル単位でしか出力できないため、
業務で全テーブルのデータを出力する場合、使いづらいものがあります。

そこで、データベース内の全テーブルをキー順にソートしてファイルに出力するバッチを
SQLServerManagementStudioを用いて作成したいと思います。

全体の流れ

  1.     データベース名の取得
  2.     データベース内の全テーブルを取得
  3.     テーブルのキーを取得
  4.     キーのカラムを取得
  5.     SQLCMDのコマンド文を編集して出力
  6.     SQLServerManagementStudioに出力されたSQLCMDのコマンド文をバッチファイルとして保存

の以上になります。
なお、今回作成するSQLCMDのコマンド文ですが、SQLServer認証用です。
Windows認証用を作成する場合、21行目の内容を変更してください。 

以下が今回実行するSQL文になります。
15行目~18行目のインスタンス名、ユーザ名、パスワード、ファイルの出力パスは適宜、適切な値に変更してください。

DECLARE @servername         nvarchar(128)
DECLARE @user               nvarchar(128)
DECLARE @password           nvarchar(128)
DECLARE @dbname             nvarchar(128)
DECLARE @sqlcmdconst        varchar(4000)
DECLARE @sqlcmd             varchar(4000)
DECLARE @outputpath         varchar(255)
DECLARE @order              varchar(4000)
DECLARE @tablename          nvarchar(128)
DECLARE @keyname            nvarchar(128)
DECLARE @keycolumn          nvarchar(128)

-- インスタンス名、ユーザ名、パスワード、出力するパスは環境に合わせて書き換える
SET @servername = 'HOGE\SQLEXPRESS'   -- インスタンス名
SET @user = 'user'                    -- ユーザ名
SET @password = 'password'            -- パスワード
SET @outputpath = 'C:\test\'          -- 出力するパス
SET @sqlcmdconst = 'sqlcmd -Q"SET NOCOUNT ON SELECT * FROM [table] [order]" -o"[outputfile]" -S[server] -d[database] -U[user] -P[password] -s,'
SET @order = ''

-- データベース名を取得する
SELECT TOP 1 @dbname = TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES

-- データベース内の全テーブルを取得する
DECLARE tablelist CURSOR FOR
   SELECT
      A.TABLE_NAME
   FROM
      INFORMATION_SCHEMA.TABLES A
         LEFT JOIN
            INFORMATION_SCHEMA.VIEWS B ON
            A.TABLE_NAME = B.TABLE_NAME
   WHERE
      B.TABLE_CATALOG IS NULL

-- 全テーブルを取得するカーソルをオープン
OPEN tablelist
FETCH NEXT FROM tablelist INTO @tablename
WHILE @@FETCH_STATUS = 0
   BEGIN
      -- テーブルのキー名を取得する
      DECLARE keyname CURSOR FOR
         SELECT
            name
         FROM
            sys.indexes
         WHERE
            object_id = OBJECT_ID(@tablename) AND
            is_primary_key = 1

      -- テーブルのキー名を取得するカーソルをオープン
      OPEN keyname
      FETCH NEXT FROM keyname INTO @keyname
      IF @@FETCH_STATUS = 0
         BEGIN
            -- キーのカラムを取得
            DECLARE keycolumn CURSOR FOR
               SELECT
                  COLUMN_NAME
               FROM
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
               WHERE
                  CONSTRAINT_NAME = @keyname AND
                  TABLE_NAME = @tablename
               ORDER BY
                  ORDINAL_POSITION

            -- キーカラムを取得するカーソルをオープン
            OPEN keycolumn
            FETCH NEXT FROM keycolumn INTO @keycolumn
            WHILE @@FETCH_STATUS = 0
               BEGIN
                  SET @order = @order + @keycolumn + ','
                  FETCH NEXT FROM keycolumn INTO @keycolumn
               END

            -- キーのカラムを取得するカーソルのクローズ&解放
            CLOSE keycolumn
            DEALLOCATE keycolumn

            -- 末尾のカンマを削除
            SET @order = 'ORDER BY ' + SUBSTRING(@order, 1 , LEN(@order) - 1)
         END
      CLOSE keyname
      DEALLOCATE keyname

      -- SQLCMDの編集
      SET @sqlcmd = REPLACE(@sqlcmdconst, '[table]', @tablename)
      SET @sqlcmd = REPLACE(@sqlcmd, '[order]', @order)
      SET @sqlcmd = REPLACE(@sqlcmd, '[outputfile]', @outputpath + @tablename + '.csv')
      SET @sqlcmd = REPLACE(@sqlcmd, '[server]', @servername)
      SET @sqlcmd = REPLACE(@sqlcmd, '[database]', @dbname)
      SET @sqlcmd = REPLACE(@sqlcmd, '[user]', @user)
      SET @sqlcmd = REPLACE(@sqlcmd, '[password]', @password)
      PRINT @sqlcmd
      SET @order = ''
      FETCH NEXT FROM tablelist INTO @tablename
   END

-- 全テーブルを取得するカーソルのクローズ&解放
CLOSE tablelist
DEALLOCATE tablelist

 

1.データベース名の取得

今回はINFORMATION_SCHEMA.TABLESから取得していますが、
接続しているデータベース名が取得できればなんでもよいです。

SELECT TOP 1 @dbname = TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES</p>

2.データベース内の全テーブルを取得

INFORMATION_SCHEMA.TABLESから取得しますが、ビューも含まれているため、INFORMATION_SCHEMA.VIEWSとLEFT JOINで結合し、INFORMATION_SCHEMA.VIEWSにないレコードのみ抽出しています。

DECLARE tablelist CURSOR FOR
    SELECT
        A.TABLE_NAME
    FROM
        INFORMATION_SCHEMA.TABLES A
    LEFT JOIN
        INFORMATION_SCHEMA.VIEWS B ON
        A.TABLE_NAME = B.TABLE_NAME
    WHERE
        B.TABLE_CATALOG IS NULL


3.テーブルのキーを取得

sys.indexesから取得します。

sys.indexesには主キー以外の情報も含まれています。主キーのレコードはis_Primary_keyカラムが 1 になっているため、テーブル名から求めたOBJECT_IDが一致し且つis_Primary_keyカラムが 1 のレコードを抽出しています。

DECLARE keyname CURSOR FORSELECTnameFROMsys.indexesWHEREobject_id = OBJECT_ID(@tablename) ANDis_primary_key = 1</td>


4.キーのカラムを取得

INFORMATION_SCHEMA.KEY_COLUMN_USAGEから取得します。
今までに取得したテーブル名とキー名を用いて取得します。
ORDINAL_POSITIONを昇順でソートしていますが、ORDINAL_POSITIONは主キーのカラム順です。
ソートをしない場合、SQLCMDで出力した結果のソート順がおかしくなる可能性があります。
 

DECLARE keycolumn CURSOR FOR
    SELECT
        COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
        CONSTRAINT_NAME = @keyname AND
        TABLE_NAME = @tablename
    ORDER BY
        ORDINAL_POSITION

5.SQLCMDのコマンド文を編集して出力

上記で取得した情報を基にSQLCMDのコマンド文を編集して、PRINT文でメッセージウィンドウに出力します。

-- SQLCMDの編集
SET @sqlcmd = REPLACE(@sqlcmdconst, '[table]', @tablename)
SET @sqlcmd = REPLACE(@sqlcmd, '[order]', @order)
SET @sqlcmd = REPLACE(@sqlcmd, '[outputfile]', @outputpath + @tablename + '.csv')
SET @sqlcmd = REPLACE(@sqlcmd, '[server]', @servername)
SET @sqlcmd = REPLACE(@sqlcmd, '[database]', @dbname)
SET @sqlcmd = REPLACE(@sqlcmd, '[user]', @user)
SET @sqlcmd = REPLACE(@sqlcmd, '[password]', @password)

PRINT @sqlcmd


6.
SQLServerManagementStudioに出力されたSQLCMDのコマンド文をバッチファイルとして保存

メッセージウィンドウに出力されているSQLCMDのコマンド文をコピーして、バッチファイルとして保存して実行します。

▼ 実行結果

実行結果_SQL_バッチ

最後に

テーブルの内容をファイルに出力する際に、
少しでも力になれればと思い、紹介させていただきました。
 
最後まで御覧いただきありがとうございます。