読者です 読者をやめる 読者になる 読者になる

SQL Serverのメタデータへのクエリ

SQL Serverのオブジェクト情報(テーブルやビューなどの情報)は、システムカタログ(sys.〜)に格納されています。

http://msdn.microsoft.com/ja-jp/library/ms189082.aspx

私は「SQL Server mangaement studio」のGUIで、これらのオブジェクトの設定を調べるのが面倒なとき、繰り返しチェックするとき、全テーブルに対するSQLを書くのが大変なとき、に使うことが多いです。

だいたい、こんな感じで使ってます。

--全テーブルのデータを削除するDELETE文を生成
SELECT
       'DELETE FROM ' + name /*DELETE文*/
FROM sys.objects  /*システムカタログ*/
WHERE
       type = 'U'  /*ユーザ作成テーブル*/
ORDER BY name
--全テーブルのデータ件数を取得するSELECT文を生成
SELECT
       'SELECT ''' + name + ''' AS テーブル名, COUNT(*) AS 行数 FROM ' + name + ' UNION ALL '
FROM sys.objects  /*システムカタログ*/
WHERE
       type = 'U'  /*ユーザ作成テーブル*/
ORDER BY name

--最後のUNION ALLだけ削除
--全テーブルのカラム名と長さを取得するSQL
SELECT
       OBJ.name AS テーブル名
       ,COL.column_id AS 列番
       ,COL.name AS 列名
       ,TYP.name AS 型名
       ,COL.max_length 長さ
FROM sys.columns AS COL
       INNER JOIN (
               SELECT *
               FROM sys.objects
               WHERE TYPE = 'U'
               ) AS OBJ ON
                       OBJ.object_id = COL.object_id
       INNER JOIN sys.types AS TYP ON /*型情報*/
                       TYP.user_type_id = COL.user_type_id
ORDER BY OBJ.name,COL.column_id
--外部キーの取得
SELECT
       FK.name AS 制約名,
       OBJ_ORG.name AS テーブル名,
       OBJ_REF.name AS 親テーブル名
FROM sys.foreign_keys AS FK
       INNER JOIN sys.objects AS OBJ_ORG ON
       OBJ_ORG.object_id = FK.parent_object_id
       INNER JOIN sys.objects AS OBJ_REF ON
       OBJ_REF.object_id = FK.referenced_object_id