最新版テーブル定義出力クエリ、及び一時テーブルのテーブル定義確認

## 【序文】
一時テーブルのテーブル定義を確認しようとして少し嵌ったので忘備録として残す。

## 【環境】
| SQLServer |
| ---- |
| 2017 |
設定はデフォルト。

## 【テーブル定義の確認方法】
一般的なテーブル定義の確認方法は以下。
[テーブルの定義の表示 - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/view-the-table-definition?view=sql-server-2017 "テーブルの定義の表示 - SQL Server _ Microsoft Docs")

システムストアドプロシージャを利用する場合は以下。
[カタログ ストアド プロシージャ (TRANSACT-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/catalog-stored-procedures-transact-sql?view=sql-server-2017 "カタログ ストアド プロシージャ (TRANSACT-SQL) - SQL Server _ Microsoft Docs")
[sp_help (TRANSACT-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-2017 "sp_help (TRANSACT-SQL) - SQL Server _ Microsoft Docs")
[sp_tables (TRANSACT-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-tables-transact-sql?view=sql-server-2017 "sp_tables (TRANSACT-SQL) - SQL Server _ Microsoft Docs")
[sp_columns (TRANSACT-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-columns-transact-sql?view=sql-server-2017 "sp_columns (TRANSACT-SQL) - SQL Server _ Microsoft Docs")

しかし、これらの方法で一時テーブルを確認するも出てこない。？？？
探し方が悪い？

## 【一時テーブルはどこにある？】
[CREATE TABLE (Transact-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-table-transact-sql?redirectedfrom=MSDN&view=sql-server-2017 "CREATE TABLE (Transact-SQL) - SQL Server _ Microsoft Docs")

> tempdb の sysobjects テーブルに格納される一時テーブルのフル ネームは、CREATE TABLE ステートメントで指定されたテーブル名とシステムが生成する数値サフィックスから構成されます。

そうそう、一時テーブルは `tempdb` に作成される。それは知っていたけれど……。
前述システムストアドプロシージャはカレントのデータベースに対して機能する。つまり、明示的に `tempdb` に切り替える必要がある。

まず一時テーブル作成。

```SQL
SELECT * INTO #TempTbl FROM (VALUES ('A', 1))t([Col1], [Col2]);
```

`tempdb` 直下に一時テーブルが作成される。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/d0107e06-9abf-cac3-4819-19d896ba561f.png" width=20%>

次に一時テーブルの確認。

```SQL
USE tempdb;
GO 
EXEC sp_tables @table_name = N'%';
EXEC sp_help N'#TempTbl';
EXEC sp_columns @table_name = N'#TempTbl';
GO
```

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/4aa5ba23-5b85-b85b-0613-f77a46bcffeb.png" width=50%>

## 【最新版テーブル定義出力クエリ】
久しぶりにテーブル定義関連のシステムビューについて調べてみると、こんな情報が。

[sys.sysobjects (TRANSACT-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-2017 "sys.sysobjects (TRANSACT-SQL) - SQL Server _ Microsoft Docs")
> この SQL Server 2000 システム テーブルは、下位互換性を保つためにビューとして含まれています。 代わりに、現在の SQL Server システム ビューを使用することをお勧めします。 対応するシステム ビューを調べるには、「システム テーブルのシステム ビューへのマッピング (Transact-SQL)」をご覧ください。 この機能は、Microsoft SQL Server の将来のバージョンで削除されます。 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

[システム テーブルのマッピング システム ビュー (TRANSACT-SQL) - SQL Server _ Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql?view=sql-server-2017 "システム テーブルのマッピング システム ビュー (TRANSACT-SQL) - SQL Server _ Microsoft Docs")

うーむ、いつの間にそんなことに。
テーブル定義を出力するクエリってのは定番ネタだと思うけど、改めてぐぐってみても、参照しているビューが古かったり、作りがイマイチだったり。
そんな訳で実装してみた。カレントデータベースが対象。

```SQL
DECLARE @SchemaName     sysname = N'dbo';       -- スキーマ名
DECLARE @TableName      sysname = N'Table_1';   -- テーブル名

SELECT
      [Col].name                                            [名称]              -- 列の名前。オブジェクト内で一意。
    , [Typ].name                                            [型]                -- 型
    , CASE
        WHEN [Typ].user_type_id IN (106, 108)                                   THEN LTRIM(STR([Col].precision))        -- decimal, numeric
        WHEN [Typ].user_type_id IN (165, 167, 231) AND [Col].max_length = -1    THEN N'MAX'                             -- varbinary, varchar, nvarchar
        WHEN [Typ].user_type_id IN (231, 239)                                   THEN LTRIM(STR([Col].max_length / 2))   -- nvarchar, nchar
        WHEN [Typ].user_type_id IN (165, 167, 173, 175)                         THEN LTRIM(STR([Col].max_length))       -- varbinary, varchar, binary, char
        WHEN [Typ].user_type_id IN (60, 61, 122) OR [Typ].scale = 0             THEN N''                                -- money, datetime, smallmoney
        ELSE                                                                         LTRIM(STR([Col].scale))            -- time, datetime2, datetimeoffset
      END                                                   [桁数]              -- 数値ベースの場合は、列の有効桁数。
    , IIF([Typ].user_type_id IN (106, 108), LTRIM(STR([Col].scale)), N'')                                               -- decimal, numeric
                                                            [小数部]            -- 数値に基づく場合は列の小数点以下桁数
    , LEFT(N'○', [Col].is_nullable)                        [NULLを許容]        -- 1 = 列で NULL 値を使用できます
    , ISNULL(LTRIM(STR([Idx].index_column_id)), '')         [PK]                -- PK
    , ISNULL([Prp].value, N'')                              [コメント]          -- コメント
FROM sys.tables                                             [Tbl]               -- テーブル情報
INNER JOIN sys.columns                                      [Col]               -- 列情報
    ON  [Tbl].object_id         = [Col].object_id                               -- オブジェクトの ID
INNER JOIN sys.types                                        [Typ]               -- オブジェクトの ID
    ON  [Col].user_type_id      = [Typ].user_type_id                            -- 型の内部システム型の ID
LEFT OUTER JOIN sys.index_columns                           [Idx]               -- インデクス
    ON  [Tbl].object_id         = [Idx].object_id                               -- オブジェクトの ID
    AND [Col].column_id         = [Idx].column_id                               -- 列の ID
    AND [Idx].index_id          = 1                                             -- PK
LEFT OUTER JOIN sys.extended_properties                     [Prp]               -- 拡張プロパティ
    ON  [Tbl].object_id         = [Prp].major_id                                -- オブジェクトの ID
    AND [Col].column_id         = [Prp].minor_id                                -- 拡張プロパティが属するアイテムのセカンダリ ID
    AND [Prp].class             = 1                                             -- プロパティが存在するアイテムのクラスを識別します。1 = オブジェクトまたは列
    AND [Prp].class_desc        = N'OBJECT_OR_COLUMN'                           -- 拡張プロパティが存在するクラスの説明
    AND [Prp].name              = N'MS_Description'                             -- class、major_id、および minor_id で一意となるプロパティ名
WHERE   [Tbl].schema_id         = SCHEMA_ID(@SchemaName)                        -- スキーマ ID
    AND [Tbl].object_id         = OBJECT_ID(@SchemaName + N'.' + @TableName)    -- オブジェクトの ID
ORDER BY
      [Col].[column_id]         -- 列の ID
;
```

真面目に実装すると、桁数の判定がすげー面倒。
それと PK。公式ヘルプには記載が見当たらないようだけど、`sys.index_columns.index_id` が 1 のものが PK のようだったのでそう判定している。違ったらゴメンってことで。


## 【参照公式ヘルプ】
| システムビュー/システム関数 | 説明 |
| ---- | ---- |
| [sys.columns](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-columns-transact-sql?view=sql-server-2017 "sys.columns (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | ビューやテーブルなど、列を持つオブジェクトの列ごとに 1 行のデータを返します |
| [sys.extended_properties](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties?view=sql-server-2017 "sys.extended_properties (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | 現在のデータベース内の拡張プロパティごとに 1 行のデータを返します |
| [sys.index_columns](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-index-columns-transact-sql?view=sql-server-2017 "sys.index_columns (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | 列の一部であるごとに 1 行が含まれています、 sys.indexesインデックスまたは順序付けられていないテーブル (ヒープ) |
| [sys.indexes](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017 "sys.indexes (Transact SQL) - SQL Server _ Microsoft Docs") | テーブル、ビュー、テーブル値関数など、テーブル オブジェクトのインデックスまたはヒープごとに 1 行のデータを格納します |
| [sys.objects](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-2017 "sys.objects (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | ネイティブ コンパイルのスカラー ユーザー定義関数を含む、データベース内で作成されるユーザー定義のスキーマ スコープ オブジェクトごとに行が含まれています |
| [sys.schemas](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/schemas-catalog-views-sys-schemas?view=sql-server-2017 "sys.schemas (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | データベース スキーマごとに 1 行が含まれています |
| [sys.tables](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-2017 "sys.tables (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | 各ユーザー テーブルの行を返します |
| [sys.types](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-types-transact-sql?view=sql-server-2017 "sys.types (TRANSACT-SQL) - SQL Server _ Microsoft Docs") | システム型とユーザー定義の型ごとに 1 行のデータを格納します |
| [SCHEMA_ID](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/schema-id-transact-sql?view=sql-server-2017 "SCHEMA_ID (Transact-SQL) - SQL Server _ Microsoft Docs") | スキーマ名に関連付けられているスキーマ ID を返します |
| [OBJECT_ID](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/object-id-transact-sql?view=sql-server-2017 "OBJECT_ID (Transact-SQL) - SQL Server _ Microsoft Docs") | スキーマ スコープ オブジェクトのデータベース オブジェクト ID 番号を返します |