系統平臺：
Microsoft Windows (64-bit) 2012
版本：
5.6.5
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 = '表名'; |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69994931/viewspace-2794418/，如需轉載，請註明出處，否則將追究法律責任。