たんたかさんの カラム名検索でテーブル検索 を見てなるほどと思ったので少しまとめてみたいと思います。
SQL Server では オブジェクト カタログ ビュー があり、このビューを確認することによりさまざまなオブジェクトの情報を確認することができます。
冒頭で紹介したたんたかさんのブログでは、 sys.objects
sys.columns
を使用してカラム名やデータ型を取得していました。
よく使うものとして以下のようなオブジェクト カタログビューがあります。
オブジェクト カタログ ビュー名 | 用途 |
sys.objects | 各種オブジェクトの一覧を取得 |
sys.tables | テーブルの一覧を取得 |
sys.columns | 列の一覧を取得 |
sys.procedures | ストアドプロシージャの一覧を取得 |
sys.foreign_keys | FK の一覧を取得 |
sys.indexes | インデックスの一覧を取得 |
sys.check_constraints | CHECK 制約の一覧を取得 |
sys.key_constraints | |
sys.stats | 統計情報の一覧を取得 |
sys.sql_modules sys.system_sql_modules sys.all_sql_modules |
ストアドプロシージャやビュー等の定義を取得 # sp_helptext を使用することで特定のオブジェクトの定義を取得することもできます。 |
sys.parameters sys.all_parameters sys.system_parameters |
ストアドプロシージャのパラメータ (引数) を取得 |
また メタデータ関数 を組み合わせることで必要な情報の条件を簡単に絞り込むことができます。
私は [sys] スキーマの情報を除くために以下のように SCHEMA_ID を使ったりします。
SELECT * FROM sys.objects WHERE schema_id <> SCHEMA_ID(‘sys’) |
こちらもよく使うものを軽くまとめてみたいと思います。
メタデータ関数名 | 用途 |
DB_ID | データベース名を ID に変換 |
DB_NAME | データベース ID を名前に変換 |
SCHEMA_ID | スキーマ 名を ID に変換 |
SCHEMA_NAME< /td> | スキーマ ID を名前に変換 |
STATS_DATE | 統計情報の更新日を取得 |
TYPE_ID | データ型の ID を名前に変換 |
ほかにも ~ PROPERTY (OBJECTPROPERTYEX / INDEXPROPERTY / DATABASEPROPERTYEX / SERVERPROPERTY 等) といようなプロパティの情報を取得するものもありますのでこの辺も
SQL Database を意識した情報取得をしようと思った場合には、クラスター化インデックスやプライマリキーの設定 (DataSync を想定) についての情報を取得したいということがあるかと思います。
その場合は、以下のようなクエリを実行すると取得できます。
SQL Azure でテーブルのサイズを取得 のクエリと併用すると現状の把握としてよいかもしれないですね。
クラスター化インデックスの設定状況の確認
SELECT st.name,st.object_id , si.name FROM sys.tables st LEFT JOIN sys.indexes si ON st.object_id = si.object_id AND si.type = 1 WHERE schema_id <> SCHEMA_ID(‘sys’) ORDER BY st.name ASC |
プライマリキーの設定状況の確認
SELECT st.name,st.object_id , skc.name FROM sys.tables st LEFT JOIN sys.key_constraints skc ON st.object_id = skc.parent_object_id WHERE st.schema_id <> SCHEMA_ID(‘sys’) ORDER BY st.name ASC |
オブジェクトカタログビューやメタデータ関数は SQL Server 特有のものですが、ISO 標準定義の INFORMATION_SCHEMA を使用して各種情報を取得することもできます。
以下のようなクエリを実行するとテーブルの情報を取得することができます。
SELECT * FROM TEST.INFORMATION_SCHEMA.TABLES |
SQL Server の RDBMS 以外でも使用できるクエリを作成する場合にはこれを使用するとよいかも。
# INFORMATION_SCHEMA で情報を取得する方法を知っていると他の RDBMS で情報を取得したい場合に流用できるかと。