データベース内の全テーブル (table) を取得する

データベース内の全テーブル (table) を取得するには？

データベース内の全テーブル (table) の一覧を取得したり、テーブルの名前の一部を指定して、検索したりしたいような時があるかもしれません。

何通りか方法があるので、データベース内のユーザが定義した全テーブルの一覧を取得するスクリプトをご紹介します。

データベース内の全テーブル (table) を取得する

まず、sys.tables というシステムカタログビューから現在のデータベース内のユーザー定義テーブルの一覧を取得することができます。

テーブル一覧を取得したいデータベースを選択して、以下のクエリーを実行します。

SELECT	S.name AS SchemaName,
	T.name AS TableName
FROM	sys.tables AS T
	   INNER JOIN sys.schemas AS S
	      ON T.schema_id = S.schema_id
ORDER BY S.name,
	 T.name;

これを使って、例えば、テーブル名に Person が入るテーブルの一覧を取得したい時は次のようにできます。

SELECT	S.name AS SchemaName,
	T.name AS TableName
FROM	sys.tables AS T
	   INNER JOIN sys.schemas AS S
	      ON T.schema_id = S.schema_id
WHERE	T.name LIKE '%Person%'
ORDER BY S.name,
	 T.name;

先ほどの sys.tables は、sys.objects というシステムカタログビューからユーザーテーブルのみをフィルターして、テーブル情報のカラムを何個か足したものなので、以下のようにしてもユーザー定義のテーブルの一覧を取得することができます。

sys.objects の type = 'U' がユーザー定義のテーブルです。

SELECT	S.name AS SchemaName,
	O.name AS TableName
FROM	sys.objects AS O
	   INNER JOIN sys.schemas AS S
	      ON O.schema_id = S.schema_id
WHERE	type = 'U' -- U = Table (user-defined)
ORDER BY S.name,
	 O.name;

他には、INFORMATION_SCHEMA.TABLES というシステムインフォメーションスキーマービューでも、テーブルの一覧を取得できます。

ユーザー定義のテーブルの情報を取得したい時は TABLE_TYPE = 'BASE TABLE' を指定します。

SELECT	TABLE_SCHEMA,
	TABLE_NAME
FROM	INFORMATION_SCHEMA.TABLES
WHERE	TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA,
	 TABLE_NAME;

いろいろ方法はありますが、テーブルのスキーマと名前だけが必要な時はどれを使っても良いと思います。 残りのカラムが違いますので、必要に応じて確認してください。

どの方法でも、現在のユーザーにパーミッションがある table しか取得できませんので、十分権限のあるユーザーでログインして実行してくださいね。

以上、データベース内のユーザが定義した全テーブルの一覧を取得するスクリプトをご紹介しました。