PostgreSQL系統(tǒng)相關(guān)查詢[1]
來源：程序員人生 發(fā)布時間：2015-03-23 07:57:41 閱讀次數(shù)：4524次
PG 系統(tǒng)查詢腳本
--查詢用戶進程及文本
SELECT a.pid AS Id, a.usename AS User, a.client_addr AS Host, a.client_port AS Port, datname AS db, query AS Command, a.state AS Idle, l.mode as State, query_start AS Time
, CASE WHEN c.relname IS NOT NULL THEN 'Locked Object: ' || c.relname ELSE 'Locked Transaction:' || l.virtualtransaction END AS Info
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON a.pid = l.pid
LEFT JOIN pg_class c ON l.relation = c.oid
ORDER BY a.pid ASC, c.relname ASC
--查詢表
[2015-03-03 17:38:35.668] [002232] [localhost] [PGSQL]
SELECT tablename, schemaname FROM pg_tables WHERE schemaname='public' ORDER BY schemaname, tablename
--查詢視圖
[2015-03-03 17:38:35.731] [002232] [localhost] [PGSQL]
SELECT viewname, schemaname FROM pg_views WHERE schemaname='public' ORDER BY schemaname, viewname
--查詢所屬視圖及其定義
SELECT c.oid, c.relname, c.relacl, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition, obj_description(c.oid), n.nspname
FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'v'::"char" AND n.nspname = 'public'
--查詢架構(gòu)名下表、表注釋及表行數(shù)
SELECT c.oid, obj_description(c.oid), c.relhasoids AS hasoids, n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers, c.relacl, c.reltuples,
((SELECT count(*) FROM pg_inherits WHERE inhparent = c.oid) > 0) AS inhtable, i2.relname AS inhtablename, c.reloptions AS param
FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN (pg_inherits i INNER JOIN pg_class c2 ON i.inhparent = c2.oid) i2 ON i2.inhrelid = c.oid
WHERE (c.relkind = 'r'::"char") AND n.nspname = 'public'
order by reltuples
--查詢
數(shù)據(jù)庫及所屬用戶和字符集
SELECT d.datname, d.oid, pg_get_userbyid(d.datdba) AS owner, shobj_description(d.oid, 'pg_database') AS comment, t.spcname, d.datacl
, d.datlastsysoid, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname
FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace=t.oid
--EOF
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機掃描二維碼進行捐贈
