今回の記事は、以前このブログで書かせていただきました、「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