人大金仓：元数据查询语句记录

一、获取表描述

select c.relname TABLE_NAME,d.description table_comment from SYS_CLASS as c inner join SYS_NAMESPACE as n on c.relnamespace = n.oid inner join SYS_DESCRIPTION as d on c.oid = d.classoid where n.nspname = '%s' and c.relname in (%s)

二、获取视图DDL

SELECT * FROM information_schema.views WHERE TABLE_NAME = '%s' and TABLE_SCHEMA = '%s'

三、字段详情查询

"select c.table_catalog,c.table_schema TABLE_SCHEMA,c.table_name TABLE_NAME,c.column_name COLUMN_NAME,c.column_default DATA_DEFAULT,c.is_nullable,c.data_type DATA_TYPE,c.character_maximum_length ,c.numeric_precision_radix RADIX,c.numeric_precision NUMERIC_PRECISION,c.numeric_scale,c.ordinal_position, p.column_name as pri\n" +
            "from information_schema.COLUMNS as c\n" +
            "left join \n" +
            "(select sys_constraint.contype,sys_namespace.nspname as table_schema,sys_class.relname as table_name ,sys_attribute.attname as column_name,sys_type.typname as data_type,sys_constraint.conname as index_name\n" +
            "from sys_constraint\n" +
            "inner join sys_class on sys_constraint.conrelid = sys_class.oid\n" +
            "inner join sys_attribute on sys_attribute.attrelid = sys_class.oid and sys_attribute.attnum = sys_constraint.conkey[1]\n" +
            "inner join sys_type on sys_type.oid = sys_attribute.atttypid\n" +
            "inner join sys_namespace on sys_constraint.connamespace = sys_namespace.oid\n" +
            "where sys_constraint.contype = 'p') as p\n" +
            "on c.table_schema = p.table_schema and c.table_name = p.table_name and c.column_name = p.column_name\n" +
            "where c.table_name = '%s' and c.table_schema = '%s'";

四、查询当前用户是否存在

select usename FROM sys_user where usename = '%s'

五、查询库列表

SELECT schema_name SCHEMA_NAME FROM information_schema.schemata

六、查询表列表：过滤掉分区表

select table_name from information_schema.TABLES where table_schema = '%s' and table_type = '%s' and table_name not in (SELECT child.relname partition_table_name FROM  sys_inherits JOIN sys_class parent ON sys_inherits.inhparent = parent.oid JOIN sys_class child ON sys_inherits.inhrelid = child.oid JOIN sys_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN sys_namespace nmsp_child  ON nmsp_child.oid = child.relnamespace WHERE nmsp_parent.nspname = '%s' and nmsp_child.nspname = '%s')

七、查询分区信息

select a.relname table_name,sys_attribute.attname column_name,sys_partitioned_table.partstrat from sys_partitioned_table inner join (select relname,oid from sys_class where relname in ('%s') and relnamespace = ('"%s"'::regnamespace) ) a on sys_partitioned_table.partrelid=a.oid inner join sys_attribute on sys_attribute.attrelid = a.oid and  sys_attribute.attnum = any(sys_partitioned_table.partattrs)

八、查询分区约束信息

SELECT parent.relname table_name , child.relname partition_table_name , sys_get_expr(child.relpartbound, child.oid, true) as partition_expression FROM  sys_inherits JOIN sys_class parent ON sys_inherits.inhparent = parent.oid JOIN sys_class child ON sys_inherits.inhrelid = child.oid JOIN sys_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN sys_namespace nmsp_child  ON nmsp_child.oid = child.relnamespace WHERE parent.relname in ('%s') and nmsp_parent.nspname = '%s' and nmsp_child.nspname = '%s'