スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

テーブル情報の取得

SQL Serverに関する話です。

テーブル構造に関する情報(変数名や型、Primary Keyなど)を取得する方法です。

ココを参考にさせていただきました。

 

DB情報を取得するマクロ

-- マクロ登録 ---------------
create procedure DBInfo
as
BEGIN
    /* テーブル定義情報を取得 */
    SELECT              DB_NAME()                       AS db_name
             	      , SCHEMA_NAME(tables.schema_id)   AS schema_name
            	      , tables.name                     AS table_name
            	      , tables.type_desc                AS table_type
            	      , extProp.value                   AS table_comment
        FROM            sys.tables tables
	    /* コメントデータは拡張プロパティシステムビューに存在する */
        LEFT OUTER JOIN sys.extended_properties extProp
                    ON  extProp.class    = 1                /* テーブルの場合、classの値は常に1 (OBJECT_OR_COLUMN) */
    			    AND tables.object_id = extProp.major_id /* テーブルのobject_idはmajor_idと対応する */
    			    AND extProp.minor_id = 0                /* テーブルの場合、minor_idの値は常に0 */
        ORDER BY        db_name, schema_name, table_name
    ;
END
go

-- 使い方 --;
DBInfo

 

テーブル情報を取得するマクロ

-- マクロ登録 -----------------------------
create procedure TableInfo
    @tblname nvarchar(255)
as
BEGIN
    SELECT              DB_NAME()                         AS db_name
                      , SCHEMA_NAME(tables.schema_id)     AS schema_name
                      , tables.name                       AS table_name
                      , columns.name                      AS column_name
                      , columns.column_id                 AS ordinal_position
                        /* PKか否かの判別フラグ */
                      , CAST(CASE WHEN pk_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_primary_key
                      , pk_cols.constraint_name           AS pk_constraint_name
                      , pk_cols.key_ordinal               AS pk_key_ordinal
                        /* ユニークキー(UQ)か否かの判別フラグ */
                      , CAST(CASE WHEN uq_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_unique_key
                      , uq_cols.constraint_name           AS uq_constraint_name
                      , uq_cols.key_ordinal               AS uq_key_ordinal
                      , TYPE_NAME(columns.system_type_id) AS column_data_type
                      , defaultConstraints.definition     AS column_default
                      , columns.is_nullable               AS is_nullable
                      , columns.max_length                AS max_length
                      , columns.precision                 AS precision
                      , columns.scale                     AS scale
                      , extProp.value                     AS column_comment

        FROM            sys.tables                                      tables
        INNER JOIN      sys.columns                                     columns
                    ON  tables.object_id = columns.object_id
        /* DEFAULT制約定義を取得する為に以下の二つのテーブルを外部結合 */
        LEFT OUTER JOIN sys.sysconstraints                              constraints
                    ON  columns.object_id = constraints.id
                    AND columns.column_id = constraints.colid
                    /* DEFAULT制約を表す疑似ビットマスク値 (マスクしていないと取得できない場合があるとのこと. (thanks murasukeさん) */
                    AND (constraints.status & 2069) = 2069               
        LEFT OUTER JOIN sys.default_constraints                         defaultConstraints
                    ON  constraints.constid = defaultConstraints.object_id
                    AND tables.schema_id    = defaultConstraints.schema_id
        /* コメントデータは拡張プロパティシステムビューに存在する */
        LEFT OUTER JOIN sys.extended_properties                         extProp
                    ON  extProp.class = 1  /* カラムの場合、classの値は常に1 (OBJECT_OR_COLUMN) */
                    AND columns.object_id = extProp.major_id
                    AND columns.column_id = extProp.minor_id
        /* プライマリーキーの情報を取得する為に以下の情報を外部結合 */
        LEFT OUTER JOIN (
            SELECT          key_const.name       AS constraint_name
                          , idx_cols.key_ordinal AS key_ordinal
                          , cols.name            AS col_name
                          , cols.object_id       AS col_object_id
                          , cols.column_id       AS col_column_id
            FROM            sys.tables                                  tbls
            /* PKの情報を結合 */
            INNER JOIN      sys.key_constraints                         key_const
                        ON  tbls.object_id = key_const.parent_object_id
                        AND key_const.type = 'PK'
            /* 対応するインデックス情報からカラムと特定 */
            INNER JOIN      sys.index_columns                           idx_cols
                        ON  key_const.parent_object_id = idx_cols.object_id
                        AND key_const.unique_index_id  = idx_cols.index_id
            INNER JOIN      sys.columns                                 cols
                        ON  idx_cols.object_id = cols.object_id
                        AND idx_cols.column_id = cols.column_id
        )                                                               pk_cols
                    ON  columns.object_id = pk_cols.col_object_id
                    AND columns.column_id = pk_cols.col_column_id
        /* ユニークキーの情報を取得する為に以下の情報を外部結合 */
        LEFT OUTER JOIN (
            SELECT          key_const.name       AS constraint_name
                          , idx_cols.key_ordinal AS key_ordinal
                          , cols.name            AS col_name
                          , cols.object_id       AS col_object_id
                          , cols.column_id       AS col_column_id
            FROM            sys.tables                                  tbls
            /* UQ(ユニークキー)の情報を結合 */
            INNER JOIN      sys.key_constraints                         key_const
                        ON  tbls.object_id = key_const.parent_object_id
                        AND key_const.type = 'UQ'
            /* 対応するインデックス情報からカラムと特定 */
            INNER JOIN      sys.index_columns                           idx_cols
                        ON  key_const.parent_object_id = idx_cols.object_id
                        AND key_const.unique_index_id  = idx_cols.index_id
            INNER JOIN      sys.columns                                 cols
                        ON  idx_cols.object_id = cols.object_id
                        AND idx_cols.column_id = cols.column_id
        )                                                               uq_cols
                    ON  columns.object_id = uq_cols.col_object_id
                    AND columns.column_id = uq_cols.col_column_id

        WHERE           tables.name = @tblname
        ORDER BY        db_name
                      , schema_name
                      , table_name
                      , columns.column_id
    ;
END
GO

-- 使い方 -------------------------
TableInfo 'tbl_test';

 

以上です

スポンサーサイト

トラックバック

コメント

コメントを残す

Secret



上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。