「テーブル定義はあるけど、全然最新化されてねぇじゃねぇか！！」
ってこと、よくあるよね...
そんなときにSQL一発で全部のテーブル定義を抽出できるSQLをつくってみた。
※ただしPostgreSQLにかぎる
テーブル定義の確認
実際のSQL
テーブル名、列名のコメントと一緒に取ってくるようにしている。
もし作成時にコメントをつけていなかったら、pg_descriptionとinner joinしているところを変更すれば、
物理名だけで取ってこれるはず。
2020/11/19:追記
PKの情報も一緒に持ってくるようにしてみた。
WITH table_define AS(
SELECT
isc.ordinal_position,
isc.table_name,
table_pgd.description,
isc.column_name,
pgd.description,
CASE
WHEN ccu.column_name = isc.column_name THEN '○'
ELSE NULL
END AS is_pk,
isc.data_type,
isc.character_maximum_length,
isc.column_default,
isc.is_nullable
FROM
information_schema.columns isc
INNER JOIN
pg_stat_user_tables psut
ON psut.relname = isc.table_name
AND psut.schemaname = 'スキーマ名'
INNER JOIN
pg_description pgd
ON pgd.objoid = psut.relid
AND pgd.objsubid = isc.ordinal_position
INNER JOIN
information_schema.table_constraints tc
ON tc.table_name = isc.table_name
AND tc.table_schema = 'スキーマ名'
AND tc.constraint_type = 'PRIMARY KEY'
LEFT OUTER JOIN
information_schema.constraint_column_usage ccu
ON ccu.table_catalog = tc.table_catalog
AND ccu.table_schema = tc.table_schema
AND ccu.table_name = ccu.table_name
AND ccu.constraint_name = tc.constraint_name
INNER JOIN
(
SELECT
*
FROM
pg_description pgd
WHERE
pgd.objsubid = 0
) table_pgd
ON table_pgd.objoid = psut.relid
WHERE
isc.table_schema = 'スキーマ名'
)
SELECT
*
FROM
table_define td
ORDER BY
td.table_name,
td.is_pk NULLS LAST,
td.ordinal_position
ちょっとだけ説明
・information_schema とはデータベース内の様々メタデータを取得するために標準SQLで定められているビューの集合
・pg_descriptionには、コメントが格納されている
「objoid」でテーブルを特定し、「objsubid」でカラムを特定する。
「information_schema.columns」から、「objsubid」は取得できる。
・テーブルは「pg_stat_user_tables」から「objoid」を取得することでコメントの取得が可能。
UNIQUEインデックスを一覧で取得するSQL
業務で必要になって使ったので追記。
select
a2.nspname as namespase,
a4.relname as table_name,
a1.relname as index_name,
a5.attname as column_name,
position(cast(a5.attnum as text) in replace(cast(a3.indkey as text), ' ', '')) as index_position
--インデックスの定義順特定
from
pg_class a1,
pg_namespace a2,
pg_index a3,
pg_class a4,
pg_attribute a5
where
a1.relkind = 'i'
--インデックスのみ取得
and a1.relnamespace = a2.oid
--スキーマ（namespase）特定用
and a1.oid = a3.indexrelid
--インデックスの情報取得
and a3.indrelid = a4.oid
--インデックスが登録されているテーブルの名前特定用
and a5.attnum = any(a3.indkey)
-- インデックスのカラム特定
and a5.attrelid = a3.indrelid
and a3.indisunique = true
and a3.indisprimary = false
and a2.nspname = 'namespace'
order by
namespase,
table_name,
index_name,
index_position
-- インデックスの定義順でソート
;