標籤:des blog http io ar os 使用 for 檔案
項目組要出表結構的文檔,手寫太麻煩,想用slq指令碼匯出一份。
--查詢所有的表欄位資訊(帶表名)
select
(select relname||‘--‘||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name,
a.attname as column_name,
format_type(a.atttypid,a.atttypmod) as data_type,
(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype=‘p‘)>0 then ‘Y‘ else ‘N‘ end) as 主鍵約束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype=‘u‘)>0 then ‘Y‘ else ‘N‘ end) as 唯一約束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype=‘f‘)>0 then ‘Y‘ else ‘N‘ end) as 外鍵約束,
(case when a.attnotnull=true then ‘Y‘ else ‘N‘ end) as nullable,
col_description(a.attrelid,a.attnum) as comment
from pg_attribute a
where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind =‘r‘ and relname like ‘exg_%‘))
order by table_name,a.attnum;
執行sql語句,然後將查詢結果匯出
使用英文逗號做分隔字元,匯出csv檔案,可以直接在excle中編輯
使用本地字元集可以防止亂碼
?
附:其它sql指令碼
--查詢表名和描述
select relname as table_name,(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where relkind =‘r‘ and relname like ‘exg_%‘ order by table_name;
?
--查詢表欄位資訊
select
a.attname as column_name,
format_type(a.atttypid,a.atttypmod) as data_type,
(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype=‘p‘)>0 then ‘Y‘ else ‘N‘ end) as 主鍵約束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype=‘u‘)>0 then ‘Y‘ else ‘N‘ end) as 唯一約束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype=‘f‘)>0 then ‘Y‘ else ‘N‘ end) as 外鍵約束,
(case when a.attnotnull=true then ‘Y‘ else ‘N‘ end) as nullable,
col_description(a.attrelid,a.attnum) as comment
from pg_attribute a
where attstattarget=-1 and attrelid = (select oid from pg_class where relname =‘exg_ms_alarm‘);--表名
Postgresql 匯出表結構資訊