PostgreSQL获取数据库中所有table
名:
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
PostgreSQL获取数据库中所有table
名及table
的注解信息:
SELECT tablename, obj_description(relfilenode, 'pg_class') FROM pg_tables a, pg_class b WHERE a.tablename = b.relname AND a.tablename NOT LIKE 'pg%' AND a.tablename NOT LIKE 'sql_%' ORDER BY a.tablename;
PostgreSQL
获取指定table
的所有字段信息:
SELECT col_description(a.attrelid, a.attnum) AS comment, format_type(a.atttypid, a.atttypmod) AS type, a.attname AS name, a.attnotnull AS notnull FROM pg_class AS c,pg_attribute AS a WHERE c.relname = 'tablename' AND a.attrelid = c.oid AND a.attnum>0
select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum
select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0; select * from pg_tables;
select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0); select * from pg_class;
select relname as tabname, cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relname ='tbl_alarm';
select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description from pg_class c,pg_attribute a,pg_type t,pg_description d where c.relname='tbl_alarm' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum;