PostgreSQL 查询数据表、视图信息

--获得指定schema范围内的所有表和视图的列表，可指定一个排除表前缀模式

with param as (select 'public,iit' as schema_name,'db2g%' as exclude_pattern),base_info as (--获得所有基表select pg_namespace.nspname as schema_name, a.relname as tbl_name ,'TBL' as tbl_type, b.description as tbl_commentfrom pg_class ajoin pg_namespace on a.relnamespace=pg_namespace.oidleft join (select * from pg_description where objsubid =0 ) bon a.oid = b.objoidwhere a.relkind='r'and a.relname not like (select exclude_pattern from param)and pg_namespace.nspname in (select regexp_split_to_table(schema_name,',') from param)union all---获取所有视图SELECT schemaname as schema_name, viewname as tbl_name,'VW' as tbl_type, null as tbl_commentFROM pg_viewsWHERE schemaname in (select regexp_split_to_table(schema_name,',') from param))
select * from base_info;

--获得指定schema范围内的所有表和视图的数据列信息，可指定一个排除表前缀模式

with param as (select 'public,iit' as schema_name,'db2g%' as exclude_pattern),base_info as (select table_schema, table_name, ordinal_position                                                      as Colorder, column_name                                                           as ColumnName, data_type                                                             as TypeName, coalesce(character_maximum_length, numeric_precision, -1)             as Length, numeric_scale                                                         as Scale, case is_nullable when 'NO' then 0 else 1 end                          as CanNull, column_default                                                        as DefaultVal, case when position('nextval' in column_default) > 0 then 1 else 0 end as IsIdentity, case when b.pk_name is null then 0 else 1 end                         as IsPK, c.DeTextfrom information_schema.columnsleft join (select pg_attr.attname as colname,pg_constraint.conname as pk_name,pg_class.relname as tblname,pg_namespace.nspname as schema_namefrom pg_constraintjoin pg_class on pg_constraint.conrelid = pg_class.oidjoin pg_attribute pg_attr on pg_attr.attrelid = pg_class.oidand pg_attr.attnum = pg_constraint.conkey[1]join pg_type on pg_type.oid = pg_attr.atttypidjoin pg_namespace on pg_constraint.connamespace=pg_namespace.oidwhere pg_constraint.contype = 'p') b on b.colname = information_schema.columns.column_nameand b.tblname=information_schema.columns.table_nameand b.schema_name=information_schema.columns.table_schemaleft join (select attname, description as DeText, pg_class.relname as tblname,pg_namespace.nspname as schema_namefrom pg_classjoin pg_namespace on pg_class.relnamespace=pg_namespace.oidleft join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oidleft join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelidand pg_desc.objsubid = pg_attr.attnumwhere pg_attr.attnum > 0and pg_attr.attrelid = pg_class.oid) c on c.attname = information_schema.columns.column_nameand c.tblname=information_schema.columns.table_nameand c.schema_name=information_schema.columns.table_schemawhere table_schema in (select regexp_split_to_table(schema_name,',') from param)and table_name not like (select exclude_pattern from param)order by table_name,ordinal_position)
select * from base_info;

--查询指定模式下的表和视图with param as (select 'public' as schema_name,'db2g%' as exclude_pattern)
--获得所有基表
select a.relname as tbl_name ,'TBL' as tbl_type, b.description as tbl_comment
from pg_class aleft join (select *from pg_description where objsubid =0 ) bon a.oid = b.objoid
where a.relname in (select tablenamefrom pg_tableswhere schemaname = (select schema_name from param))and a.relname not like (select exclude_pattern from param)
union all
---获取所有视图
SELECT viewname as tbl_name,'VW' as tbl_type, null as tbl_comment
FROM pg_views
WHERE schemaname =(select schema_name from param)
order by tbl_name asc;

--查询指定数据基表的列信息

with param as (select 'emp' as tblname),base_info as (select ordinal_position                                                      as Colorder, column_name                                                           as ColumnName, data_type                                                             as TypeName, coalesce(character_maximum_length, numeric_precision, -1)             as Length, numeric_scale                                                         as Scale, case is_nullable when 'NO' then 0 else 1 end                          as CanNull, column_default                                                        as DefaultVal, case when position('nextval' in column_default) > 0 then 1 else 0 end as IsIdentity, case when b.pk_name is null then 0 else 1 end                         as IsPK, c.DeTextfrom information_schema.columnsleft join (select pg_attr.attname as colname, pg_constraint.conname as pk_namefrom pg_constraintjoin pg_class on pg_constraint.conrelid = pg_class.oidjoin pg_attribute pg_attr on pg_attr.attrelid = pg_class.oidand pg_attr.attnum = pg_constraint.conkey[1]join pg_type on pg_type.oid = pg_attr.atttypidwhere pg_class.relname = (select tblname from param)and pg_constraint.contype = 'p') b on b.colname = information_schema.columns.column_nameleft join (select attname, description as DeTextfrom pg_classleft join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oidleft join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelidand pg_desc.objsubid = pg_attr.attnumwhere pg_attr.attnum > 0and pg_attr.attrelid = pg_class.oidand pg_class.relname = (select tblname from param)) c on c.attname = information_schema.columns.column_namewhere table_schema = 'public'and table_name = (select tblname from param)order by ordinal_position asc)
select * from base_info