1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| -- listing database from server
SELECT * FROM pg_database;
-- listing users
SELECT * FROM pg_user;
-- listing groups
SELECT * FROM pg_group;
-- listing schemas
SELECT * FROM pg_namespace
-- listing tables from a schema
SELECT * FROM pg_tables WHERE schemaname = 'myschema';
SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');
-- listing tables from a schema > COLUMNS
SELECT * FROM information_schema.columns WHERE table_schema = 'myschema' AND table_name='mytable';
-- listing tables from a schema > CONSTRAINTS
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 = 'mytable'AND tc.constraint_name = 'mytable_pkey';
-- listing tables from a schema > CONSTRAINTS > PRIMARY KEY
SELECT * FROM pg_statio_user_indexes WHERE schemaname = 'myschema' AND relname='mytable';
SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'myschema' AND table_name = 'mytable';
-- listing views from a schema
SELECT * FROM pg_views WHERE schemaname = 'myschema';
SELECT table_name FROM information_schema.views WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name !~ '^pg_';
-- listing types from a schema
-- listing sequences from a schema
SELECT * FROM pg_statio_user_sequences WHERE schemaname = 'myschema';
SELECT * FROM pg_class WHERE relkind = 'S' AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema');
-- listing functions from a schema
SELECT * FROM information_schema.routines WHERE specific_schema NOT IN ('pg_catalog', 'information_schema') AND type_udt_name != 'trigger'
-- listing triggers from a schema
SELECT * FROM information_schema.triggers WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema')
|