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

今回の記事では、SQLCLRで、テーブルレイアウト情報をテーブル形式で取得する関数を作ってみます。

テーブルレイアウトそのものをテーブルの形式で求めるというのは、このブログでも以前書かせて頂きましたが、それをSQLCLRで関数化してしまうと、かなり応用が利いて便利だと思います。

関数には、サーバー名、データベース名、テーブル名を指定出来るようにしてあります。
環境につきましては、私のものなので、ご自分の環境に適宜読み替えて頂ければと思います。
なお、SQL-Server 2008との接続文字列ですが、「context connection = true」を使いたいのは山々なのですが、これを使ってしまうと、アセンブリ(DLL)からシステムテーブルにアクセスが禁止されてしまい使えませんでした。



■　SQLCLR関数側ソース　■

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

//------------------------------------
//テーブル情報取得関数
//------------------------------------
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "SQLCLR_GetTableInformation_StreamRow",
TableDefinition = "FIELD_NO smallint,FIELD_NAME nvarchar(max),FIELD_LENGTH smallint,FIELD_COLLATION nvarchar(max)",
DataAccess = DataAccessKind.Read
)]
public static IEnumerable SQLCLR_GetTableInformation(SqlString p_svr,SqlString p_db,SqlString p_tbl)
{
List useList_rec = new List(); //結果格納エリア
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=xxx;Password=xxx";
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 += "T.colid  AS [FIELD_NO], ";
str_sql += "T.name   AS [FIELD_NAME],";
str_sql += "T2.name  AS [FIELD_TYPE],";
str_sql += "T.length AS [FIELD_LENGTH],";
str_sql += "ISNULL(T.collation,'') ";
str_sql += "AS [FIELD_COLLATION] ";
str_sql += "FROM #####DB#####.dbo.sysobjects AS M ";
str_sql += "INNER JOIN #####DB#####.dbo.syscolumns AS T ";
str_sql += "ON(M.id = T.id) ";
str_sql += "INNER JOIN #####DB#####.dbo.systypes AS T2 ";
str_sql += "ON(T.xusertype = T2.xusertype) ";
str_sql += "WHERE M.id = OBJECT_ID('#####DB#####.dbo.#####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.GetInt16(0);
    use_rec.FIELD_NAME = obj_red.GetString(1);
    use_rec.FIELD_TYPE = obj_red.GetString(2);
    use_rec.FIELD_LENGTH = obj_red.GetInt16(3);
    use_rec.FIELD_COLLATION = obj_red.GetString(4);
    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 SqlInt16  FIELD_NO,
out SqlString FIELD_NAME,
out SqlString FIELD_TYPE,
out SqlInt16  FIELD_LENGTH,
out SqlString FIELD_COLLATION
)
{
stc_TableInfo use_receive = (stc_TableInfo)obj_delivery;
FIELD_NO = (SqlInt16)use_receive.FIELD_NO;
FIELD_NAME = (SqlString)use_receive.FIELD_NAME;
FIELD_TYPE = (SqlString)use_receive.FIELD_TYPE;
FIELD_LENGTH = (SqlInt16)use_receive.FIELD_LENGTH;
FIELD_COLLATION = (SqlString)use_receive.FIELD_COLLATION;
}

■　SQL-Server側　■

-- *******************************************************
-- 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        smallint,
FIELD_NAME      nvarchar(max),
FIELD_TYPE      nvarchar(max),
FIELD_LENGTH    smallint,
FIELD_COLLATION nvarchar(max)
)
AS EXTERNAL NAME
ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_GetTableInformation;

■　レイアウトがテーブル化されていると便利な例　■

テーブルレイアウトがテーブル化されていると、クエリーでさまざまな操作が可能になります。たとえば、フィールド名を一行に並べて表示するという事も、再帰クエリーと組み合わせれば一文で可能です。

WITH
CTE_SOURCE
AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY FIELD_NO)   AS "KEY_NO",
ROW_NUMBER() OVER(ORDER BY FIELD_NO)+1 AS "NEXT_KEY_NO",
CAST(FIELD_NAME AS nvarchar(max))      AS "FIELD_NAME"
FROM
SPDB.dbo.SQLCLR_GetTableInformation('ASPIRE1410\sqlexpress','tempdb','##T_TEST')
),
CTE_TARGET
AS
(
SELECT
MAX(KEY_NO) AS "KEY_NO"
FROM CTE_SOURCE
),
CTE_CORE
AS
(
--アンカー部
SELECT
KEY_NO      AS "KEY_NO",
NEXT_KEY_NO AS "NEXT_KEY_NO",
FIELD_NAME  AS "LIST"
FROM CTE_SOURCE
WHERE KEY_NO = 1
--再帰部
UNION ALL
SELECT
T.KEY_NO      AS "KEY_NO",
T.NEXT_KEY_NO AS "NEXT_KEY_NO",
M.LIST + ',' + T.FIELD_NAME
            AS "LIST"
FROM CTE_CORE AS M
INNER JOIN CTE_SOURCE AS T
ON(M.NEXT_KEY_NO = T.KEY_NO)
)
SELECT
M.LIST
FROM CTE_CORE AS M
INNER JOIN CTE_TARGET AS T
ON(M.KEY_NO = T.KEY_NO)
OPTION (MAXRECURSION 0);