RootsLabs

Pour un logiciel, j’ai eu besoin de récupérer la liste des tables, et puis curieux de nature, j’ai poussé la recherche un peu plus loin. Donc voici la liste de mes résultats. Hésitez pas si il vous manque des informations…

-- 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')