如何通过SQL 的方式获得数据库中的一些关键信息,是一个DB最正常的工作,如何通过一些SQL来获得PG的一些关键的参数和信息或者是数据库中的一些信息是需要知道的一件事情。以下是部分 1
一般来说每种数据库中都有一个或几个系统的数据库,在PG中schemaname 以 pg_catalog开头都是系统表,通过系统表我们就可以了解的大部分数据库系统所做的事情
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 = '表名';
7 对某个约束的详细信息的展示
SELECT
c.conname AS
constraint_name,
CASE
c.contype
WHEN
'c'
THEN
'CHECK'
WHEN
'f'
THEN
'FOREIGN KEY'
WHEN
'p'
THEN
'PRIMARY KEY'
WHEN
'u'
THEN
'UNIQUE'
END
AS
"constraint_type",
CASE
WHEN
c.condeferrable = 'f'
THEN
0 ELSE
1 END
AS
is_deferrable,
CASE
WHEN
c.condeferred = 'f'
THEN
0 ELSE
1 END
AS
is_deferred,
t.relname AS
table_name,
array_to_string(c.conkey, ' ') AS
constraint_key,
CASE
confupdtype
WHEN
'a'
THEN
'NO ACTION'
WHEN
'r'
THEN
'RESTRICT'
WHEN
'c'
THEN
'CASCADE'
WHEN
'n'
THEN
'SET NULL'
WHEN
'd'
THEN
'SET DEFAULT'
END
AS
on_update,
CASE
confdeltype
WHEN
'a'
THEN
'NO ACTION'
WHEN
'r'
THEN
'RESTRICT'
WHEN
'c'
THEN
'CASCADE'
WHEN
'n'
THEN
'SET NULL'
WHEN
'd'
THEN
'SET DEFAULT'
END
AS
on_delete,
CASE
confmatchtype
WHEN
'u'
THEN
'UNSPECIFIED'
WHEN
'f'
THEN
'FULL'
WHEN
'p'
THEN
'PARTIAL'
END
AS
match_type,
t2.relname AS
references_table,
array_to_string(c.confkey, ' ') AS
fk_constraint_key
FROM
pg_constraint c
LEFT
JOIN
pg_class t ON
c.conrelid = t.oid
LEFT
JOIN
pg_class t2 ON
c.confrelid = t2.oid
WHERE
t.relname = '表名'
AND
c.conname = '约束名';
8 列出相关的自增序列
SELECT relname
FROM pg_class
WHERE relkind = 'S'
AND relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);
9 筛选相关数据库中建立的trigger
SELECT
DISTINCT
trigger_name
FROM
information_schema.triggers
WHERE
trigger_schema NOT
IN
('pg_catalog', 'information_schema');
以及关于 trigger 详细的信息
SELECT *
FROM information_schema.triggers
WHERE trigger_schema NOT IN
('pg_catalog', 'information_schema');
10 查看系统中创建的函数
SELECT routine_name
FROM information_schema.routines
WHERE specific_schema NOT IN
('pg_catalog', 'information_schema')
AND type_udt_name != 'trigger';
11 查看当前数据库中表的主键
SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE tc.table_name !~ '^(pg_|sql_)'
AND tc.constraint_type = 'PRIMARY KEY';
12 获得索引与表之间的关系
SELECT a.index_name, b.attname
FROM (
SELECT a.indrelid,
c.relname index_name,
unnest(a.indkey) index_num
FROM pg_index a,
pg_class b,
pg_class c
WHERE
b.oid=a.indrelid
AND a.indisprimary != 't'
AND a.indexrelid=c.oid
) a,
pg_attribute b
WHERE a.indrelid = b.attrelid
AND a.index_num = b.attnum and a.index_name !~ '^(pg_|sql_)'
ORDER BY a.index_name, a.index_num
13 显示VIEW 之间的依赖关系
SELECT v.relname AS "dependent_view",
t.relname AS "referenced_relation"
FROM pg_depend dv
LEFT JOIN pg_class v ON v.oid = dv.refobjid
LEFT JOIN pg_namespace nv ON v.relnamespace = nv.oid
LEFT JOIN pg_depend dt
ON dv.classid = dt.classid
AND dv.objid = dt.objid
AND dv.refobjid <> dt.refobjid
AND dv.refclassid = dt.refclassid
AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
AND dv.refclassid = 'pg_catalog.pg_class'::regclass
LEFT JOIN pg_class t ON t.oid = dt.refobjid
LEFT JOIN pg_namespace nt
ON t.relnamespace = nt.oid
AND nv.nspname = 'public'
AND nt.nspname = 'public'
WHERE dv.deptype = 'i'
AND v.relkind = 'v'
AND t.relkind IN ('r', 'v')
AND v.relname = 'testttt' -- VIEW NAME
GROUP BY v.relname, t.relname;
本文分享自 AustinDatabases 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!