SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

オブジェクトの情報を取得するテーブル等

leave a comment

たんたかさんの カラム名検索でテーブル検索 を見てなるほどと思ったので少しまとめてみたいと思います。

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 スキーマ 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

image

プライマリキーの設定状況の確認

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

image

オブジェクトカタログビューやメタデータ関数は SQL Server 特有のものですが、ISO 標準定義の INFORMATION_SCHEMA  を使用して各種情報を取得することもできます。

以下のようなクエリを実行するとテーブルの情報を取得することができます。

SELECT
*
FROM
TEST.INFORMATION_SCHEMA.TABLES

image

SQL Server の RDBMS 以外でも使用できるクエリを作成する場合にはこれを使用するとよいかも。
# INFORMATION_SCHEMA で情報を取得する方法を知っていると他の RDBMS で情報を取得したい場合に流用できるかと。

Written by masayuki.ozawa

5月 11th, 2013 at 1:26 pm

Posted in SQL Server

Tagged with

Leave a Reply

*