SQLCLRで、テーブルレイアウト情報をテーブル形式で取得する関数を作る (第二弾)

今回の記事は、以前このブログで書かせていただきました、「SQL CLRにおいて、テーブルレイアウト情報をテーブル形式で取得する関数を作る」という記事の第二弾です。

前回は、テーブルのレイアウト情報を3つのシステムテーブル(sysobjects、syscolumns、systypes)から取得していましたが、よりシンプルな書き方が可能なINFORMATION_SCHEMA.COLUMNSから取得してみます。

INFORMATION_SCEMAでは足りない情報は、systypesから補足します。
なお、環境は適宜、ご自分の環境に読み替えて下さいませ。

■　基本となるSQL文　■

SELECT
CAST(M.ORDINAL_POSITION AS int) AS [FIELD_NO],
M.COLUMN_NAME                   AS [FIELD_NAME],
M.DATA_TYPE                     AS [FIELD_TYPE],
CAST(ISNULL(M.CHARACTER_MAXIMUM_LENGTH,T.length) AS int)
                          AS [FIELD_LENGTH],
CAST(ISNULL(M.CHARACTER_OCTET_LENGTH,T.length) AS int)
                          AS [FIELD_BYTE],
ISNULL(M.COLLATION_NAME,'')     AS [FIELD_COLLATION]
FROM データベース名.INFORMATION_SCHEMA.COLUMNS AS M
LEFT JOIN master.dbo.systypes AS T
ON(M.DATA_TYPE COLLATE Japanese_BIN = T.name COLLATE Japanese_BIN)
WHERE M.TABLE_NAME = 'テーブル名';

■ SQL CLRのソース　■
無料版のVisual C# 2008 Expressでプログラムを書けます。

//*******************************************************
//*  テーブル情報用の構造体
//*******************************************************
private struct stc_TableInfo
{
  public Int32  FIELD_NO;        //フィールドの順番
  public string FIELD_NAME;      //フィールド名
  public string FIELD_TYPE;      //フィールドの型
  public Int32  FIELD_LENGTH;    //フィールドのサイズ(定義値)
  public Int32  FIELD_BYTE;      //フィールドのサイズ(バイト値)
  public string FIELD_COLLATION; //文字列の場合は照合順序
}

//------------------------------------
//テーブル情報取得関数
//------------------------------------
[Microsoft.SqlServer.Server.SqlFunction(
 FillRowMethodName = "SQLCLR_GetTableInformation_StreamRow",
 TableDefinition =
 "FIELD_NO int,FIELD_NAME nvarchar(max),FIELD_LENGTH int,FIELD_BYTE int,FIELD_COLLATION nvarchar(max)",
 DataAccess = DataAccessKind.Read
)]
public static IEnumerable SQLCLR_GetTableInformation(SqlString p_svr,SqlString p_db,SqlString p_tbl)
{
  List＜stc_tableinfo＞ useList_rec = 
new List＜stc_tableinfo＞(); //結果格納エリア
　//＜＞は半角で入力です。
  stc_TableInfo use_rec; //データ取得エリア
  string str_con; //接続文字列格納エリア
  string str_sql; //クエリー文字列格納エリア
  SqlConnection obj_con = null;          //SQL-Server接続クラス
  SqlCommand obj_cmd = new SqlCommand(); //SQL-Serverコマンド
  SqlDataReader obj_red = null;          //SQL-Serverデータリーダー

  try
  {
      //SQL接続文字列設定(context connection = trueはだめ)
      str_con = "Data Source=#####SVR#####;User Id=XXXX;Password=XXXX";
      str_con = str_con.Replace("#####SVR#####", (string)p_svr);
      obj_con = new SqlConnection(str_con);
      obj_con.Open();
      obj_cmd.CommandTimeout = 60;
      obj_cmd.CommandType = CommandType.Text;

      str_sql = "SELECT ";
      str_sql+= "CAST(M.ORDINAL_POSITION AS int) AS [FIELD_NO], ";
      str_sql+= "M.COLUMN_NAME                   AS [FIELD_NAME], ";
      str_sql+= "M.DATA_TYPE                     AS [FIELD_TYPE], ";
      str_sql+= "CAST(ISNULL(M.CHARACTER_MAXIMUM_LENGTH,T.length) AS int) ";
      str_sql+= "                                AS [FIELD_LENGTH], ";
      str_sql+= "CAST(ISNULL(M.CHARACTER_OCTET_LENGTH,T.length) AS int) ";
      str_sql+= "                                AS [FIELD_BYTE], ";
      str_sql+= "ISNULL(M.COLLATION_NAME,'')     AS [FIELD_COLLATION] ";
      str_sql+= "FROM #####DB#####.INFORMATION_SCHEMA.COLUMNS AS M ";
      str_sql+= "LEFT JOIN master.dbo.systypes AS T ";
      str_sql+= "ON(M.DATA_TYPE COLLATE Japanese_BIN = T.name COLLATE Japanese_BIN) ";
      str_sql+= "WHERE M.TABLE_NAME = '#####TBL#####'; ";

      str_sql = str_sql.Replace("#####DB#####", (string)p_db);
      str_sql = str_sql.Replace("#####TBL#####", (string)p_tbl);
      obj_cmd.CommandText = str_sql;
      obj_cmd.Connection = obj_con;
      obj_red = obj_cmd.ExecuteReader();

      //SQL-Serverから取得したデータを結果エリアに格納する
      //@@@@@@@@@@[LOOP-START]@@@@@@@@@@
      while (obj_red.Read())
      {
          use_rec.FIELD_NO = obj_red.GetInt32(0);
          use_rec.FIELD_NAME = obj_red.GetString(1);
          use_rec.FIELD_TYPE = obj_red.GetString(2);
          use_rec.FIELD_LENGTH = obj_red.GetInt32(3);
          use_rec.FIELD_BYTE = obj_red.GetInt32(4);
          use_rec.FIELD_COLLATION = obj_red.GetString(5);
          useList_rec.Add(use_rec);
      }
      //@@@@@@@@@@[LOOP-END  ]@@@@@@@@@@

  }
  catch (System.Exception obj_err)
  {   //例外が発生したらSQL-Serverにthrowして実行を強制停止する。
      throw obj_err;
  }
  finally
  {
      if (obj_con != null)
      {
          obj_con.Close();
          obj_con.Dispose();
      }
      if (obj_red != null)
      {
          obj_red.Close();
          obj_red.Dispose();
      }
      obj_cmd.Dispose();
  }

  return useList_rec; //ストリーム部にデータを送信する。
}
//ストリーム部(1レコードあたりの処理を記述しているのに注意)
private static void SQLCLR_GetTableInformation_StreamRow(
  object obj_delivery, //関数部から1レコード受け取り用
  out SqlInt32  FIELD_NO,
  out SqlString FIELD_NAME,
  out SqlString FIELD_TYPE,
  out SqlInt32  FIELD_LENGTH,
  out SqlInt32  FIELD_BYTE,
  out SqlString FIELD_COLLATION
)
{
  stc_TableInfo use_receive = (stc_TableInfo)obj_delivery;
  FIELD_NO = (SqlInt32)use_receive.FIELD_NO;
  FIELD_NAME = (SqlString)use_receive.FIELD_NAME;
  FIELD_TYPE = (SqlString)use_receive.FIELD_TYPE;
  FIELD_LENGTH = (SqlInt32)use_receive.FIELD_LENGTH;
  FIELD_BYTE = (SqlInt32)use_receive.FIELD_BYTE;
  FIELD_COLLATION = (SqlString)use_receive.FIELD_COLLATION;
}

■　SQL-Server 2008 (Express)側の定義　■

-- *******************************************************
-- SQLCLRの関数インターフェース
-- *******************************************************
-- ----------------------------------------------
-- テーブルレイアウト情報を取得する
-- (パラメータ説明)
-- @p_svr : サーバー名
-- @p_db  : データベース名
-- @p_tbl : テーブル名
-- ----------------------------------------------
CREATE FUNCTION SQLCLR_GetTableInformation(
@p_svr nvarchar(max),
@p_db  nvarchar(max),
@p_tbl nvarchar(max)
)
RETURNS TABLE(
 FIELD_NO        int,
 FIELD_NAME      nvarchar(max),
 FIELD_TYPE      nvarchar(max),
 FIELD_LENGTH    int,
 FIELD_BYTE      int,
 FIELD_COLLATION nvarchar(max)
)
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_GetTableInformation;
GO