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

【序文】

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

【環境】

SQLServer
2017
設定はデフォルト。

【テーブル定義の確認方法】

一般的なテーブル定義の確認方法は以下。
テーブルの定義の表示 - SQL Server _ Microsoft Docs

システムストアドプロシージャを利用する場合は以下。
カタログ ストアド プロシージャ (TRANSACT-SQL) - SQL Server _ Microsoft Docs
sp_help (TRANSACT-SQL) - SQL Server _ Microsoft Docs
sp_tables (TRANSACT-SQL) - SQL Server _ Microsoft Docs
sp_columns (TRANSACT-SQL) - SQL Server _ Microsoft Docs

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

【一時テーブルはどこにある？】

CREATE TABLE (Transact-SQL) - SQL Server _ Microsoft Docs

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

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

まず一時テーブル作成。

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

tempdb 直下に一時テーブルが作成される。

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

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

【最新版テーブル定義出力クエリ】

久しぶりにテーブル定義関連のシステムビューについて調べてみると、こんな情報が。

sys.sysobjects (TRANSACT-SQL) - SQL Server _ Microsoft Docs

この SQL Server 2000 システム テーブルは、下位互換性を保つためにビューとして含まれています。 代わりに、現在の SQL Server システム ビューを使用することをお勧めします。 対応するシステム ビューを調べるには、「システム テーブルのシステム ビューへのマッピング (Transact-SQL)」をご覧ください。 この機能は、Microsoft SQL Server の将来のバージョンで削除されます。 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

システム テーブルのマッピング システム ビュー (TRANSACT-SQL) - SQL Server _ Microsoft Docs

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

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	ビューやテーブルなど、列を持つオブジェクトの列ごとに 1 行のデータを返します
sys.extended_properties	現在のデータベース内の拡張プロパティごとに 1 行のデータを返します
sys.index_columns	列の一部であるごとに 1 行が含まれています、 sys.indexesインデックスまたは順序付けられていないテーブル (ヒープ)
sys.indexes	テーブル、ビュー、テーブル値関数など、テーブル オブジェクトのインデックスまたはヒープごとに 1 行のデータを格納します
sys.objects	ネイティブ コンパイルのスカラー ユーザー定義関数を含む、データベース内で作成されるユーザー定義のスキーマ スコープ オブジェクトごとに行が含まれています
sys.schemas	データベース スキーマごとに 1 行が含まれています
sys.tables	各ユーザー テーブルの行を返します
sys.types	システム型とユーザー定義の型ごとに 1 行のデータを格納します
SCHEMA_ID	スキーマ名に関連付けられているスキーマ ID を返します
OBJECT_ID	スキーマ スコープ オブジェクトのデータベース オブジェクト ID 番号を返します