目录
环境
文档用途
详细信息
环境
系统平台：Microsoft Windows (64-bit) 2012
版本：5.6.5
文档用途
列举了一些用来获取系统信息的SQL语句
详细信息
1、查看当前所有的表（用户表）
SELECt relname FROM pg_class WHERe relname !~ ‘^(pg_|sql_)’ AND relkind = ‘r’;
或者也可以
SELECt table_name FROM information_schema.tables WHERe table_type = ‘base TABLE’
AND table_schema NOT IN (‘pg_catalog’, ‘information_schema’);
2、查看用户建立的VIEW
SELECt table_name FROM information_schema.views WHERe table_schema NOT IN
(‘pg_catalog’, ‘information_schema’) AND table_name !~ ‘^pg_’;
3、当前数据库的用户
SELECt usename FROM pg_user;
4 列出某个表的字段
SELECt a.attname FROM pg_class c, pg_attribute a, pg_type t WHERe c.relname = ‘表名’
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid;
5 查询表的索引
SELECt relname, indkey FROM pg_class, pg_index WHERe pg_class.oid = pg_index.indexrelid
AND pg_class.oid IN (
SELECt indexrelid FROM pg_index, pg_class WHERe pg_class.relname=‘表名’
AND pg_class.oid=pg_index.indrelid
AND indisunique != ‘t’
AND indisprimary != ‘t’
);
查询这个表的那些字段，被建立了索引
SELECt t.relname, a.attname, a.attnum FROM pg_index c
LEFT JOIN pg_class t ON c.indrelid = t.oid
LEFT JOIN pg_attribute a ON a.attrelid = t.oid
AND a.attnum = ANY(indkey) WHERe t.relname = ‘表名’;
获得当前数据库表的建立索引的语句
SELECt tablename,indexname,indexdef FROM pg_indexes WHERe schemaname = ‘public’
ORDER BY tablename,indexname;
6、系统中指定表建立的约束
SELECt constraint_name, constraint_type FROM information_schema.table_constraints
WHERe table_name = ‘表名’;
