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

SQL Serverにはsys.tablesやsys.objectsといった[カタログ ビュー (Transact-SQL)](https://msdn.microsoft.com/ja-jp/library/ms174365.aspx)というものが用意されています

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

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

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

#テーブルの一覧

```sql: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実行結果](https://qiita-image-store.s3.amazonaws.com/0/51655/2619a7c3-b547-0a2d-56ef-f2a32096973c.png)


#列情報の一覧

```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実行結果](https://qiita-image-store.s3.amazonaws.com/0/51655/d3f35f8b-13b3-a99b-52a4-44db70f722cc.png)


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

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

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

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

##[sys.objects (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms190324.aspx)
データベース内で作成されるユーザー定義のスキーマ スコープ オブジェクトごとに 1 行のデータを格納します。
DDL トリガーはスキーマ スコープではないため、sys.objects では表示されません。 DML と DDL の両方を含むすべてのトリガーは、sys.triggers に格納されます。 sys.triggers には、さまざまな種類のトリガーの名前スコープ ルールを混在させて格納できます。

##[sys.tables (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms187406.aspx)
sys.objects から継承した列を含む
SQL Server 内のユーザー テーブルごとに 1 行のデータを返します。

##[sys.columns (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms176106.aspx)
ビューやテーブルなど、列を持つオブジェクトの列ごとに 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)](http://msdn.microsoft.com/ja-jp/library/ms188021.aspx)カタログビューと結合する
is_nullable
is_identity
is_computed


##[sys.types (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms188021.aspx)
システム型とユーザー定義の型ごとに 1 行のデータを格納します。
system_type_id:243がユーザ定義型(と思われる)
user_type_id：データーベース内で一意。システムデータ型の場合は、user_type_id = system_type_idとなる

##[sys.indexes (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms173760.aspx)
テーブル、ビュー、テーブル値関数など、テーブル オブジェクトのインデックスまたはヒープごとに 1 行のデータを格納します。

##[sys.index_columns (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms175105.aspx)
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)](http://msdn.microsoft.com/ja-jp/library/ms176105.aspx)
指定したプロパティに対する値を返してくれる
よくわらかないのでサンプルを記載しておきます

```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)](http://msdn.microsoft.com/ja-jp/library/ms176105.aspx)を参照してください

##[sys.key_constraints (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms174321.aspx)	
主キー制約または一意制約であるオブジェクトごとに 1 行のデータを格納します。 sys.objects.type が PK と UQ のオブジェクトが含まれます。
unique_index_id：この制約を設定するために作成された、親オブジェクトに対応する一意インデックスの ID。
parent_object_id：sys.objectsに含まれている 親のID

##[共通テーブル式(CTE)](http://technet.microsoft.com/ja-jp/library/ms190766(v=sql.105).aspx)

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

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

ケース別による使い方も紹介されています
[WITH common_table_expression (Transact-SQL)](http://msdn.microsoft.com/ja-jp/library/ms175972.aspx)

##[データベース オブジェクト](http://technet.microsoft.com/ja-jp/library/ms172451(v=sql.110).aspx)

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




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


#参考：[方法 : サンプル データベースをインストールする](https://msdn.microsoft.com/ja-jp/library/8b6y4c7s.aspx)

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

1. [Northwind and pubs Sample Databases for SQL Server 2000](http://www.microsoft.com/en-us/download/details.aspx?id=23654)の 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](https://technet.microsoft.com/ja-jp/library/ms187310%28v=sql.105%29.aspx)というコマンドがありますが、2008までしか使えないので、以下のように修正

```sql: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
```

```sql: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を実行すると、データベースが作成されます