2010年1月9日土曜日

SQL-Serverで、テーブルレイアウトそのものをテーブルデータとして扱う方法


今回の記事では、SQL-Serverにおいて、テーブルのレイアウト情報そのものを、テーブルとして扱う方法について書かせて頂きたいと思います。

SQL-Serverにおいて、テーブルのレイアウトを知りたいと思ったら、sp_helpコマンドなどがあります。
ですが、テーブルそのものとして情報を扱えたら、比較や検索などで世話がありません。

たとえば、MAINDBというデータベースに、以下のようにT_TESTテーブルを作ったとします。


CREATE TABLE MAINDB.dbo.T_TEST
(
F01 text,
F02 uniqueidentifier,
F03 date,
F04 time,
F05 datetime2,
F06 datetimeoffset,
F07 tinyint,
F08 smallint,
F09 int,
F10 smalldatetime,
F11 real,
F12 money,
F13 datetime,
F14 float,
F15 sql_variant,
F16 ntext,
F17 bit,
F18 decimal,
F19 numeric,
F20 smallmoney,
F21 bigint,
F22 varbinary,
F23 varchar(50),
F24 binary,
F25 char(50),
F26 timestamp,
F27 nvarchar(50), --unicodeなので実際は100バイト
F28 nchar(50),　--unicodeなので実際は100バイト
F29 xml
);



このテーブルのレイアウト情報をテーブルそのものとして扱うには、以下のようなクエリーを書けば良いです。


-- -------------------------------
-- SQL-Server 2000でも通用する文法
-- -------------------------------
SELECT
T.colid  AS "FIELD_NO",
T.name   AS "FIELD_NAME",
T2.name  AS "FIELD_TYPE",
T.length AS "FIELD_LENGTH",
ISNULL(T.collation,'')
      AS "FIELD_COLLATION"
FROM MAINDB.dbo.sysobjects AS M
INNER JOIN MAINDB.dbo.syscolumns AS T
ON(M.id = T.id)
INNER JOIN MAINDB.dbo.systypes AS T2
ON(T.xusertype = T2.xusertype)
WHERE M.id = OBJECT_ID('MAINDB.dbo.T_TEST')
ORDER BY "FIELD_NO";

-- -------------------------------
-- SQL-Server 2008用
-- -------------------------------
SELECT
T.column_id  AS "FIELD_ID",
T.name       AS "FIELD_NAME",
T2.name      AS "FIELD_TYPE",
T.max_length AS "FIELD_LENGTH",
ISNULL(T.collation_name,'')
          AS "FIELD_COLLATION"
FROM MAINDB.sys.objects AS M
INNER JOIN MAINDB.sys.columns AS T
ON(M.object_id = T.object_id)
INNER JOIN MAINDB.sys.types AS T2
ON(T.user_type_id = T2.user_type_id)
WHERE M.object_id = OBJECT_ID('MAINDB.dbo.T_TEST')
ORDER BY "FIELD_ID";


これは、以下のように一時テーブルにも応用が出来ます。
たとえば、SELECT INTOを使って、フィールドの型も指定せずに作りまくった一時テーブルを、後から見直す・・・というような用途にも使えると思います。


-- -------------------------------
-- 一時テーブル(便宜上、あらかじめ定義しておきます)
-- -------------------------------
CREATE TABLE #T_TEST
(
F01 text,
F02 uniqueidentifier,
F03 date,
F04 time,
F05 datetime2,
F06 datetimeoffset,
F07 tinyint,
F08 smallint,
F09 int,
F10 smalldatetime,
F11 real,
F12 money,
F13 datetime,
F14 float,
F15 sql_variant,
F16 ntext,
F17 bit,
F18 decimal,
F19 numeric,
F20 smallmoney,
F21 bigint,
F22 varbinary,
F23 varchar(50),
F24 binary,
F25 char(50),
F26 timestamp,
F27 nvarchar(50), --unicodeなので実際は100バイト
F28 nchar(50),　--unicodeなので実際は100バイト
F29 xml
);

-- -------------------------------
-- SQL-Server 2000でも通用する文法
-- -------------------------------
SELECT
T.colid  AS "FIELD_NO",
T.name   AS "FIELD_NAME",
T2.name  AS "FIELD_TYPE",
T.length AS "FIELD_LENGTH",
ISNULL(T.collation,'')
      AS "FIELD_COLLATION"
FROM tempdb.dbo.sysobjects AS M
INNER JOIN tempdb.dbo.syscolumns AS T
ON(M.id = T.id)
INNER JOIN tempdb.dbo.systypes AS T2
ON(T.xusertype = T2.xusertype)
WHERE M.id = OBJECT_ID('tempdb.dbo.#T_TEST')
ORDER BY "FIELD_NO";

-- -------------------------------
-- SQL-Server 2008用
-- -------------------------------
SELECT
T.column_id  AS "FIELD_ID",
T.name       AS "FIELD_NAME",
T2.name      AS "FIELD_TYPE",
T.max_length AS "FIELD_LENGTH",
ISNULL(T.collation_name,'')
          AS "FIELD_COLLATION"
FROM tempdb.sys.objects AS M
INNER JOIN tempdb.sys.columns AS T
ON(M.object_id = T.object_id)
INNER JOIN tempdb.sys.types AS T2
ON(T.user_type_id = T2.user_type_id)
WHERE M.object_id = OBJECT_ID('tempdb.dbo.#T_TEST')
ORDER BY "FIELD_ID";


実行結果は、記事冒頭のスクリーンショット画像のようになります。
時刻: