SQLServerのテーブル定義取得用のSQL文について

Question

前提・実現したいこと

参考URLを参考として
SQLServerでテーブル定義を取得したいと考えています。
出力した定義をExcelに貼り付けたいです。

発生している問題・エラーメッセージ

一部のデータベースでサブクエリで複数結果が返って来てしまい上手く結果が取得できません。
SELECT列でサブクエリを使用して該当の列をオブジェクト番号と行番号で絞り込んでいます。
その結果複数結果が返ってきてしまうのが原因だと考えていましたが
違うテーブルで同じ列名があるDBを作成し検証してもエラーが再現せずに困っています。

該当のソースコード

--データベース定義取得用--
SELECT
    tbl.name as 'テーブル名',
    col.name as 'カラム名',
    type_name(col.user_type_id) as 'データ型',
    col.max_length as '桁数',
    CASE(
            select
                indx.is_primary_key
            from
                sys.indexes indx
                inner join
                    sys.index_columns indxcol
                on  indx.object_id = indxcol.object_id
            where
                indx.object_id = tbl.object_id
            and indxcol.column_id = col.column_id
        )
        WHEN 1 THEN '〇'
        ELSE '×'
    END as '主キー制約',
    CASE col.is_nullable
        WHEN 1 THEN '×'
        ELSE '○'
    END as 'NOTNULL制約',
    CASE(
            select
                indx.is_unique
            from
                sys.indexes indx
                inner join
                    sys.index_columns indxcol
                on  indx.object_id = indxcol.object_id
            where
                indx.object_id = tbl.object_id
            and indxcol.column_id = col.column_id
        )
        WHEN 1 THEN '〇'
        ELSE '×'
    END as '一意制約',
    CASE col.is_identity
        WHEN 1 THEN '〇' ELSE　　'×'
    END as '自動採番設定',
    CASE
        WHEN(
            select
                idcol2.seed_value as '初期値'
            from
                sys.identity_columns idcol2
                inner join
                    sys.columns col2
                on  idcol2.column_id = col2.column_id
                and col2.object_id = idcol2.object_id
            where
                idcol2.object_id = tbl.object_id
            and idcol2.column_id = col.column_id
        ) IS NULL THEN 'なし'
        ELSE(
            select
                idcol2.seed_value as '初期値'
            from
                sys.identity_columns idcol2
                inner join
                    sys.columns col2
                on  idcol2.column_id = col2.column_id
                and col2.object_id = idcol2.object_id
            where
                idcol2.object_id = tbl.object_id
            and idcol2.column_id = col.column_id
        )
    END as '自動採番初期値',
    CASE
        WHEN(
            select
                idcol2.increment_value as '増減値'
            from
                sys.identity_columns idcol2
                inner join
                    sys.columns col2
                on  idcol2.column_id = col2.column_id
                and col2.object_id = idcol2.object_id
            where
                idcol2.object_id = tbl.object_id
            and idcol2.column_id = col.column_id
        ) IS NULL THEN 'なし'
        ELSE(
            select
                idcol2.increment_value as '増減値'
            from
                sys.identity_columns idcol2
                inner join
                    sys.columns col2
                on  idcol2.column_id = col2.column_id
                and col2.object_id = idcol2.object_id
            where
                idcol2.object_id = tbl.object_id
            and idcol2.column_id = col.column_id
        )
    END as '自動採番増減値',
    CASE
        WHEN df.definition IS NULL THEN 'なし'
        ELSE REPLACE(REPLACE(df.definition, '(', ''), ')', '')
    END AS '初期値',
    CASE(
            select
                indxcol.is_included_column
            from
                sys.indexes indx
                inner join
                    sys.index_columns indxcol
                on  indx.object_id = indxcol.object_id
            where
                indx.object_id = tbl.object_id
            and indxcol.column_id = col.column_id
        )
        WHEN 0 THEN '〇'
        ELSE '×'
    END as 'インデックスキーの有無',
    CASE
        WHEN(
            select
                rftbl.name
            from
                sys.foreign_key_columns fkcol
                inner join
                    sys.tables rftbl
                on  fkcol.referenced_object_id = rftbl.object_id
            where
                fkcol.parent_object_id = tbl.object_id
            and fkcol.parent_column_id = col.column_id
        ) IS NULL THEN 'なし'
        ELSE(
            select
                rftbl.name
            from
                sys.foreign_key_columns fkcol
                inner join
                    sys.tables rftbl
                on  fkcol.referenced_object_id = rftbl.object_id
            where
                fkcol.parent_object_id = tbl.object_id
            and fkcol.parent_column_id = col.column_id
        )
    END as '参照先テーブル',
    CASE
        WHEN(
            select
                rfcol.name
            from
                sys.foreign_key_columns fkcol
                inner join
                    sys.columns rfcol
                on  fkcol.referenced_object_id = rfcol.object_id
                and fkcol.referenced_column_id = rfcol.column_id
            where
                fkcol.parent_object_id = tbl.object_id
            and fkcol.parent_column_id = col.column_id
        ) IS NULL THEN 'なし'
        ELSE(
            select
                rfcol.name
            from
                sys.foreign_key_columns fkcol
                inner join
                    sys.columns rfcol
                on  fkcol.referenced_object_id = rfcol.object_id
                and fkcol.referenced_column_id = rfcol.column_id
            where
                fkcol.parent_object_id = tbl.object_id
            and fkcol.parent_column_id = col.column_id
        )
    END as '参照先列'
from
    sys.tables tbl
    inner join
        sys.columns col
    on  tbl.object_id = col.object_id
    left outer join
        sys.default_constraints df
    on  col.column_id = df.parent_column_id
WHERE
    tbl.type = 'U'
ORDER BY
    tbl.name,
    col.name
;
--ここまで--

試したこと

違うテーブルに同じ列名が存在するのが原因かと思い
検証として新たにDBを作成し複数テーブルで同じ列名を定義しSQLを実行しましたが
エラーにはなりませんでしたが下記のDBではエラーとなっています。
（追記）：マイクロソフトが提供しているサンプルDB
エラーとなるDB
現在サブクエリを1つずつ検証中ですが、主キー制約の所でエラーとなっています。

Comments

[Orlofsky]

コードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。
他のMarkdownの機能も確認しておいてください。

[gentaro]

複雑なSQLのデバッグは大変面倒ですし、これだけだと丸投げに見えます。
エラーの内容も書いててないのもよろしくないし。

サブクエリに問題があることがわかっているのであれば、個々のサブクエリを実行してメインのクエリとの結合条件において「複数テーブルに同じ列名がある」場合にどうなるのかを確認しましょう。
SELECT句で使っているサブクエリの結合キーで集計して複数レコード返っているのであれば、そこが原因なんでしょう。

個別の部分で原因までわかっているのであれば、改善案の相談ぐらいは載ってもらえるかもしてません。

Answer 1

字下げしていないSQLを読むのも辛いので、直接の回答ではありませんが、
SQL Server CREATE TABLE 取得方法 を試してみては？

Comments

[sima9985]

回答頂きありがとうございます。
スクリプトでCREATEを生成し定義を作成してもよいのですが
複数テーブルの定義を作成しようとすると
時間がかかると思うので出来れば一括で出力出来ればと思っています。

[Orlofsky]

[質問への追記・修正依頼]で gentaro さんが書かれた事を試されては？
このSQLはご自分で全部書かれたのですか？
参考にしたURLがあったら質問に追記しましょう。
SQLは正常に動くこと確認して1コずつサブクエリを追加するものです。

質問に出力したいテーブルのCREATE TABLEと現行の結果を追記して、どこが悪いか検討しましょう。
sys.tables はいちばん下と外部キーを参照されるテーブルを取得するところ以外は不要では？sys.columnsも同様かと。

https://johobase.com/sqlserver-catalogview-table-column/ はかなり使えそうです。

[sima9985]

参考URLとアドバイスを頂きありがとうございます。
SQLは正常に動くこと確認して1コずつサブクエリを追加するものです。
＞SQLを書いたことがあまりなくサブクエリも一気に書いてしまいました。一度書いたものが動いたので他のDBでも動くはずだと思っていたので今後はしっかり動作確認をしたいと思います。

[Orlofsky]

PRIMARY KEY, INDEX, 外部キーは複数の列から構成されることもあるし、同じ列が複数のINDEXで使われることもあるから、別のSQLにするか、LOOPが使えるストアドプログラムにしてテーブル毎の最後に持って行かないと実用的ではないでしょう。
ORDER BY の最後の列はCOLUMN_NAMEではなく、COLUMN_ID(テーブル内の列の並び)かと。
良かったら、質問の最後に完成したコードを追記したり、quiita 辺りで公開すると感謝されますよ。