今回の記事では、SQLCLRで、指定した名前のフィールド情報を、全データベース(システムデータベースは除外)の全テーブルから検索する関数を作ってみます。名前はSQLCLR_SearchField()とさせて頂きます。
過去の記事において、
テーブルレイアウト情報をテーブル形式で取得する関数(SQLCLR_GetTableInformation)を作ってみましたが、今回はこれの発展形です。
関数のパラメーターに、サーバー名と、検索したいテーブルのフィールド名(一部でも可)を指定すると、全データベース、全テーブルから条件に合うフィールド情報をテーブル形式で持ってきます。
同名のフィールドが、どのテーブルに存在するのかとか、
何かあった時の調査作業に結構役立つのではないかと思います。
SQLCLR関数は、
通常のSQL関数では内部で使用する事が出来ない一時テーブルも使えます。
今回の関数では、それを利用しています。
一旦、(システムデータベースを除外した)全データベースの名前を取得しておきます。
次のフェーズにおいて、取得したデータベースについて、内包しているテーブルのフィールド(COLUMN)をドンドン検索します。
C#言語による動的クエリー実行のようなスタイルで処理を実現しているのが味噌です。
環境その他はご自分に合わせて下さいませ。
ソースは、ブログの表示が右端で切れる場合があるため
わざと変な所で改行したりしてます。
正しい改行位置を補正して読んでいただければと思います。
すみません。
■ SQLCLRのソース(無償版のVisual C# 2008で書く事が出来ます)■
//*******************************************************
//* フィールド情報サーチ用の構造体
//*******************************************************
private struct stc_FieldInfo
{
public string DB_NAME; //データベース名
public string TABLE_NAME; //テーブル名
public int FIELD_NO; //フィールドの順番
public string FIELD_NAME; //フィールド名
public string FIELD_TYPE; //フィールド型
public int FIELD_LENGTH; //フィールド定義長
public int FIELD_BYTE; //フィールドバイト数
public string FIELD_COLLATION; //フィールド日本語照合順
}
//------------------------------------
//フィールド情報検索関数
//(TableDefinitionは改行せず一行で書いて下さい)
//------------------------------------
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "SQLCLR_SearchField_StreamRow",
TableDefinition =
"DB_NAME nvarchar(max),
TABLE_NAME nvarchar(max),
FIELD_NO int,
FIELD_NAME nvarchar(max),
FIELD_TYPE nvarchar(max),
FIELD_LENGTH int,
FIELD_BYTE int,
FIELD_COLLATION nvarchar(max)",
DataAccess = DataAccessKind.Read
)]
public static IEnumerable SQLCLR_SearchField(SqlString p_svr,SqlString p_value)
{
List useList_rec = new List(); //結果格納用
stc_FieldInfo use_rec; //データ取得エリア
SqlConnection obj_con = null; //SQL-Server接続
SqlCommand obj_cmd = new SqlCommand(); //SQL-Serverコマンド
SqlDataReader obj_red = null; //SQL-Serverデータ読み込み
string str_con = ""; //SQL-Server接続文字列用エリア
string str_sql = ""; //クエリー格納エリア
string str_sql2 = ""; //クエリー格納エリア2
try
{
str_sql2 += "INSERT INTO #T_SQLCLR_WORK001 ";
str_sql2 += "SELECT ";
str_sql2 += "M.TABLE_CATALOG AS [DB_NAME], ";
str_sql2 += "M.TABLE_NAME AS [TABLE_NAME], ";
str_sql2 += "M.ORDINAL_POSITION AS [FIELD_NO], ";
str_sql2 += "M.COLUMN_NAME AS [FIELD_NAME], ";
str_sql2 += "M.DATA_TYPE AS [FIELD_TYPE], ";
str_sql2 +=
"ISNULL(M.CHARACTER_MAXIMUM_LENGTH,T.length) ";
str_sql2 += " AS [FIELD_LENGTH], ";
str_sql2 +=
"ISNULL(M.CHARACTER_OCTET_LENGTH,T.length) ";
str_sql2 += " AS [FIELD_BYTE], ";
str_sql2 += "ISNULL(M.COLLATION_NAME,'') ";
str_sql2 += " AS [FIELD_COLLATION] ";
str_sql2 += "FROM #####DB#####.
INFORMATION_SCHEMA.COLUMNS AS M ";
str_sql2 += "LEFT JOIN master.dbo.systypes AS T ";
str_sql2 += "ON(M.DATA_TYPE COLLATE Japanese_BIN = ";
str_sql2 += "T.name COLLATE Japanese_BIN) ";
str_sql2 +=
"WHERE M.COLUMN_NAME LIKE'%#####TARGET#####%'; ";
str_sql2 =
str_sql2.Replace("#####TARGET#####", (string)p_value);
//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;
obj_cmd.Connection = obj_con;
//データベースの一覧データを求めます。
str_sql = "SELECT ";
str_sql += "name ";
str_sql += "FROM master.dbo.sysdatabases ";
//システムデータベースは除外する
str_sql += "WHERE name ";
str_sql += "not in('master','tempdb','model','msdb') ";
str_sql += "ORDER BY name; ";
obj_cmd.CommandText = str_sql;
obj_red = obj_cmd.ExecuteReader();
str_sql = "CREATE TABLE #T_SQLCLR_WORK001 ";
str_sql += "( ";
str_sql += " [DB_NAME] nvarchar(max), ";
str_sql += " [TABLE_NAME] nvarchar(max), ";
str_sql += " [FIELD_NO] int, ";
str_sql += " [FIELD_NAME] nvarchar(max), ";
str_sql += " [FIELD_TYPE] nvarchar(max), ";
str_sql += " [FIELD_LENGTH] int, ";
str_sql += " [FIELD_BYTE] int, ";
str_sql += " [FIELD_COLLATION] nvarchar(max) ";
str_sql += "); ";
//取得したデータベースの分、指定したフィールド情報を検索します。
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
while (obj_red.Read())
{
str_sql += str_sql2;
str_sql = str_sql.Replace("#####DB#####",
obj_red.GetString(0));
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
str_sql += "SELECT * ";
str_sql += "FROM #T_SQLCLR_WORK001 ";
str_sql +=
"ORDER BY [DB_NAME],[TABLE_NAME],[FIELD_NO]; ";
obj_red.Close(); //一旦、SQL-Serverデータ読み込みを終了する。
obj_red = null;
obj_cmd.CommandText = str_sql; //結果の読み取りの開始
obj_red = obj_cmd.ExecuteReader();
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
while (obj_red.Read())
{
//SQL-Serverから取得したデータを構造体に格納しています。
use_rec.DB_NAME = obj_red.GetString(0);
use_rec.TABLE_NAME = obj_red.GetString(1);
use_rec.FIELD_NO = obj_red.GetInt32(2);
use_rec.FIELD_NAME = obj_red.GetString(3);
use_rec.FIELD_TYPE = obj_red.GetString(4);
use_rec.FIELD_LENGTH = obj_red.GetInt32(5);
use_rec.FIELD_BYTE = obj_red.GetInt32(6);
use_rec.FIELD_COLLATION = obj_red.GetString(7);
useList_rec.Add(use_rec); //結果をどんどん蓄積
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
}
catch (System.Exception obj_err)
{ //例外が発生した場合はSQL-Serverに投げつけて実行を強制停止させる。
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; //結果をSQL-Serverストリーミング部に送信します。
}
//ストリーム部(1レコードあたりの処理を記述しているのに注意)
private static void SQLCLR_SearchField_StreamRow(
object obj_delivery,
out SqlString DB_NAME,
out SqlString TABLE_NAME,
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_FieldInfo use_receive =
(stc_FieldInfo)obj_delivery; //関数部から1レコード受け取り用
DB_NAME = (SqlString)use_receive.DB_NAME;
TABLE_NAME = (SqlString)use_receive.TABLE_NAME;
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しか持っていないので、Express版)
-- ----------------------------------------------
-- 複数のデータベースをまたがって、指定フィールド名から情報を検索する。
-- (パラメータ説明)
-- @p_svr : サーバー名
-- @p_value : フィールド名(Likeの構文で指定可能)
-- ----------------------------------------------
CREATE FUNCTION SQLCLR_SearchField(
@p_svr nvarchar(max),@p_value nvarchar(max))
RETURNS TABLE (
[DB_NAME] nvarchar(max),
[TABLE_NAME] nvarchar(max),
[FIELD_NO] int,
[FIELD_NAME] nvarchar(max),
[FIELD_TYPE] nvarchar(max),
[FIELD_LENGTH] int,
[FIELD_BYTE] int,
[FIELD_COLLATION] nvarchar(max)
)
EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_SearchField;
go
