Help us understand the problem. What is going on with this article?

sys.tables(カタログビュー)を使って、テーブル一覧、列の情報をSQLで取得する

More than 3 years have passed since last update.

SQL Serverにはsys.tablesやsys.objectsといったカタログ ビュー (Transact-SQL)というものが用意されています

カタログ ビューは、SQL Server データベース エンジンによって使用される情報を返します。

とあるように、カタログビューを使う事で、テーブル、列を始めとするSQL Serverが管理する各種情報を参照することが可能です

このカタログビューを使って、テーブル一覧や、列の情報をSQLによって取得できるようになります
※以下SQLを実行しているのは、Microsoftから提供されている Northwind データベースです
 作成方法は、最後に記載しておきます

テーブルの一覧

summary.sql
--テーブル一覧を表示します
--ClusteredIndex、PKの有無、PKとCIが同じか、NonClusteredIndex、InsertTriggerの数、UpdateTriggerの数、レコード数を表示します
--@table_nameに検索する対象のテーブル名を部分一致の形式で指定可能です

--summary
--ここにテーブル名の検索条件を指定します
declare @table_name nvarchar(128) = '%Customer%';


select tables.name
     , MAX(CASE WHEN OBJECTPROPERTY(tables.object_id, 'TableHasClustIndex') = 1 THEN '○' ELSE '' END) [CI有無]
     , MAX(CASE WHEN OBJECTPROPERTY(tables.object_id, 'TableHasPrimaryKey') = 1 THEN '○' ELSE '' END) [PK有無]
     , MAX(CASE WHEN constraints.object_id is null then 'PKなし'
        WHEN constraints.unique_index_id = 1 then 'true'
        ELSE 'false'
        END) [PK is CI]
     --, OBJECTPROPERTY(tables.object_id, 'TableHasNonclustIndex') TableHasNonclustIndex
     , (select count(DISTINCT index_id) from sys.indexes indexes where indexes.object_id = tables.object_id and index_id <> 1) [NCIの数]
     , MAX(OBJECTPROPERTY(tables.object_id, 'TableInsertTriggerCount')) [InsertTriggerの数]
     , MAX(OBJECTPROPERTY(tables.object_id, 'TableUpdateTriggerCount')) [UpdateTriggerの数]
     , SUM(CASE WHEN tables.is_ms_shipped = 0 AND pa.index_id IN (1,0) THEN pa.rows ELSE 0 END) [行数]
  from sys.tables tables
  left join
       (select *
          from sys.key_constraints
         where type = 'PK') AS constraints
         ON constraints.parent_object_id = tables.object_id
 inner join sys.partitions pa ON pa.object_id = tables.object_id
 where tables.name like @table_name
 group by tables.object_id, tables.name
 order by tables.name;

summary.sql実行結果

列情報の一覧

detail.sql
--テーブルの列ごとの詳細を表示します
--型名、NULL許可、IDENTITY列、計算列、PKに使われている列、ClusterdIndexに使われている列、NonClusterdIndex(keyがインデックスキーで、incが付加列)の情報を一覧で表示します
--テーブル名、列名、型名の部分一致検索が可能です
--NCIの表示数は、途中にあるMAX(CASE WHEN index_col_info.ordered_index_id = 2の箇所を増やしていけば可能ですが、きれいなやり方ではないです

--detail
--ここにテーブル名、列名の検索条件を指定します
declare @table_name nvarchar(128) = '%Customer%';
declare @col_name   nvarchar(128) = '%';
declare @type_name  nvarchar(128) = '%';


WITH table_info(object_id, table_name, cols_name, column_id, is_nullable, is_identity, is_computed, type_name)
AS
(
select tables.object_id, tables.name table_name
     , cols.name cols_name, cols.column_id, cols.is_nullable, cols.is_identity, cols.is_computed
     , types.name type_name
  from (select * from sys.tables where name like @table_name) tables
 inner join
       (select * from sys.columns where name like @col_name) cols
       ON cols.object_id = tables.object_id
 inner join
       (select * from sys.types where name like @type_name) types
       ON types.user_type_id = cols.user_type_id
)
--インデックス関連情報。自身と副問い合わせで結合しているのは、index_idを連番で並べたいため
--index_id=1がCIとなるが、単純にRANKで並べ替えると、CIがないものまでordered_index_idの最小値が[1]になってしまう
--最小のindex_idをobject_id毎に取得して、index_id=1以外を+1して、順序を維持するという愚行を実施(表示時に色々面倒になるので、ここで終わらせたい)
, index_col_info(object_id, index_id, column_id, is_included_column, ordered_index_id)
AS
(
select a.object_id, a.index_id, a.column_id, a.is_included_column
     , (CASE WHEN b.min_index_id = 1 THEN 0 ELSE 1 END) + b.ordered_index_id as ordered_index_id
  from sys.index_columns a
 inner join
       (select object_id
             , index_id
             , RANK() OVER (PARTITION BY object_id ORDER BY index_id) ordered_index_id
             , (select MIN(index_id) from sys.index_columns c where c.object_id = d.object_id) min_index_id
          from sys.index_columns d
         group by object_id, index_id) AS b
         on b.object_id = a.object_id and b.index_id = a.index_id
)
select MAX(tables.table_name) [テーブル名]
     , MAX(tables.cols_name)  [列名]
     , MAX(tables.type_name)  [型名]
     , MAX(CASE WHEN tables.is_nullable = 1 THEN '○' ELSE '' END) [NULL許可]
     , MAX(CASE WHEN tables.is_identity = 1 THEN '○' ELSE '' END) [自動採番]
     , MAX(CASE WHEN tables.is_computed = 1 THEN '○' ELSE '' END) [計算列]
     , MAX(CASE WHEN index_col_info_for_pk.object_id IS NOT NULL and tables.column_id = index_col_info_for_pk.column_id THEN '○' ELSE '' END) PK
     , MAX(CASE WHEN index_col_info.ordered_index_id = 1 THEN '○'
                ELSE '' END) CI
     --この下を増やしていけば何個でも表示可能だが、ホントは検索対象に引っかかったNCIの数だけに絞ったほうが見やすいと思う
     , MAX(CASE WHEN index_col_info.ordered_index_id = 2 AND index_col_info.is_included_column = 0 THEN 'key'
                WHEN index_col_info.ordered_index_id = 2 AND index_col_info.is_included_column = 1 THEN 'inc'
                ELSE '' END) NCI1
     , MAX(CASE WHEN index_col_info.ordered_index_id = 3 AND index_col_info.is_included_column = 0 THEN 'key'
                WHEN index_col_info.ordered_index_id = 3 AND index_col_info.is_included_column = 1 THEN 'inc'
                ELSE '' END) NCI2
     , MAX(CASE WHEN index_col_info.ordered_index_id = 4 AND index_col_info.is_included_column = 0 THEN 'key'
                WHEN index_col_info.ordered_index_id = 4 AND index_col_info.is_included_column = 1 THEN 'inc'
                ELSE '' END) NCI3
     , MAX(CASE WHEN index_col_info.ordered_index_id = 5 AND index_col_info.is_included_column = 0 THEN 'key'
                WHEN index_col_info.ordered_index_id = 5 AND index_col_info.is_included_column = 1 THEN 'inc'
                ELSE '' END) NCI4
  from table_info tables
  left join index_col_info ON index_col_info.object_id = tables.object_id and index_col_info.column_id = tables.column_id
  left join
       (select *
          from sys.key_constraints
         where type = 'PK') AS constraints
         ON constraints.parent_object_id = tables.object_id
  left join index_col_info index_col_info_for_pk
         ON index_col_info_for_pk.object_id = tables.object_id
        and index_col_info_for_pk.index_id = constraints.unique_index_id
 group by tables.table_name, tables.column_id
 order by tables.table_name, tables.column_id;

detail.sql実行結果

改めてカタログビュー

上記のデータを表示するために、sys.objectsを始めとするカタログビューを使用しています

今回使用したカタログビューと関連情報について簡単に説明します

概要

sys.objectsにsqlserverが管理しているだいたいのものが入っている感じです
このキーが[object_id]で、他のsys.XXXはこの[object_id]で参照します

sys.XXXによっては、列の定義が[sys.objects]を継承しているものもあります
なのでまずは、[sys.objects]をざっくり眺めて、やりたいことに関連ありそうなsys.XXXを
オブジェクト カタログ ビュー (Transact-SQL)
から探すと、意外と簡単に目的達成できそうです

sys.objects (Transact-SQL)

データベース内で作成されるユーザー定義のスキーマ スコープ オブジェクトごとに 1 行のデータを格納します。
DDL トリガーはスキーマ スコープではないため、sys.objects では表示されません。 DML と DDL の両方を含むすべてのトリガーは、sys.triggers に格納されます。 sys.triggers には、さまざまな種類のトリガーの名前スコープ ルールを混在させて格納できます。

sys.tables (Transact-SQL)

sys.objects から継承した列を含む
SQL Server 内のユーザー テーブルごとに 1 行のデータを返します。

sys.columns (Transact-SQL)

ビューやテーブルなど、列を持つオブジェクトの列ごとに 1 行のデータを返します。 以下に、列を持つオブジェクトの種類の一覧を示します。

  • テーブル値アセンブリ関数 (FT)
  • インライン テーブル値 SQL 関数 (IF)
  • 内部テーブル (IT)
  • システム テーブル (S)
  • テーブル値 SQL 関数 (TF)
  • ユーザー テーブル (U)
  • ビュー (V)

今回使った列
object_id:この列が属するオブジェクトのID(sys.objectsを参照する)
column_id:列のID。オブジェクト内で一意。列IDは連続した値にならないことがある
system_type_id:列のシステム型のID
user_type_id:ユーザーが定義した列の型のID(基本的には、system_type_idと同じものがはいる?)型の名前を取得するには、sys.types (Transact-SQL)カタログビューと結合する
is_nullable
is_identity
is_computed

sys.types (Transact-SQL)

システム型とユーザー定義の型ごとに 1 行のデータを格納します。
system_type_id:243がユーザ定義型(と思われる)
user_type_id:データーベース内で一意。システムデータ型の場合は、user_type_id = system_type_idとなる

sys.indexes (Transact-SQL)

テーブル、ビュー、テーブル値関数など、テーブル オブジェクトのインデックスまたはヒープごとに 1 行のデータを格納します。

sys.index_columns (Transact-SQL)

sys.indexes インデックスまたは順序付けられていないテーブル (ヒープ) の一部である列ごとに 1 つの行を含みます。
index_id:列が定義されているインデックスのID
index_column_id:インデックス列の ID です。 index_column_id は、index_id 内でのみ一意です。(index_id内でのみ一意)
column_id:object_id 内の列の ID です。column_id は、object_id 内でのみ一意です。(sys.columnsと一致する)
is_descending_key:1:降順
is_included_column:1:付加列

OBJECTPROPERTY (Transact-SQL)

指定したプロパティに対する値を返してくれる
よくわらかないのでサンプルを記載しておきます

select tables.name
     , CASE OBJECTPROPERTY(tables.object_id, 'TableHasClustIndex') = 1 THEN '○' ELSE '' END [CI有無]]
  from sys.tables tables
 order by tables.name;

[TableHasClustIndex]など、指定するプロパティを変更すれば、そのオブジェクトの設定値が取得できます
色んなプロパティがありますが、OBJECTPROPERTY (Transact-SQL)を参照してください

sys.key_constraints (Transact-SQL)

主キー制約または一意制約であるオブジェクトごとに 1 行のデータを格納します。 sys.objects.type が PK と UQ のオブジェクトが含まれます。
unique_index_id:この制約を設定するために作成された、親オブジェクトに対応する一意インデックスの ID。
parent_object_id:sys.objectsに含まれている 親のID

共通テーブル式(CTE)

各ステートメントの実行スコープ内で定義される一時結果セットと考えることができます

誤解を与えかねないですが、簡単に記載すると、そのSQL内で参照可能なViewを定義するようなものです

ケース別による使い方も紹介されています
WITH common_table_expression (Transact-SQL)

データベース オブジェクト

Microsoft SQL Server Compact データベースで定義されている、いくつかのデータベース オブジェクトの最大サイズ制限を示しています。

テーブルのレコード数とか簡単にみたいなと思う事はよくあって、毎回調べたりしてました(sys.XXXってなんだか面倒な感じがしていたので)
でも見てみるとそこまで面倒ではなかったので、ここを参考にいろいろ試して貰えればと思います

参考:方法 : サンプル データベースをインストールする

こちらの手順に従えば基本OKですが、SQL Server 2012以上に入れる場合の注意事項(sp_dboptionが使えないので、ALTER DATABASEにするだけですが)ついて手順とともに簡単にまとめておきます

  1. Northwind and pubs Sample Databases for SQL Server 2000の Web サイトにアクセスして、インストーラをダウンロード
  2. インストールを実行するとコンピューターのルート フォルダーに、SQL Server 2000 Sample Databases フォルダーが追加されます (C:\SQL Server 2000 Sample Databases など)
  3. SSMSを開き、DBを作成したいSQL Serverに接続
  4. フォルダー内の、instnwnd.sqlと、instpubs.sqlをSSMSで開く
  5. それぞれのSQL内にsp_dboptionというコマンドがありますが、2008までしか使えないので、以下のように修正
instnwnd.sql
/* 
exec sp_dboption 'Northwind','trunc. log on chkpt.','true'
exec sp_dboption 'Northwind','select into/bulkcopy','true'
*/
alter database Northwind set recovery simple
instpubs.sql
/*
execute sp_dboption 'pubs' ,'trunc. log on chkpt.' ,'true'
*/
alter database pubs  set recovery simple

※設定しているコマンドの意味
'trunc. log on chkpt.','true'

SQL Server 2000 で起動し、trunc. log on chkpt. オプションに true を指定すると、データベースの復旧モデルは SIMPLE に設定されます。オプションに false を指定すると、復旧モデルは FULL に設定されます。

'select into/bulkcopy','true'

Microsoft SQL Server 2000 から使用を開始し、データベースの現在の復旧モデルが FULL に設定されている場合、select into/bulkcopy option を使用すると復旧モデルが BULK_LOGGED に再設定されます。復旧モデルを適切な方法で変更するには、ALTER DATABASE ステートメントの SET RECOVERY 句を使用します。

修正したSQLを実行すると、データベースが作成されます

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした