Extracting Information Schema Using Postgres Sql

Information schema provides information about tables, columns, views, trigger, functions and sequences in a database. We extract these information from simple SQL queries, lets we have a well maintained database and below are simple Sql statements to get various information in our database.

1/. List All Users

SELECT usename

FROM pg_user;

 

2/.List All Tables Name

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'

AND table_schema NOT IN

('pg_catalog', 'information_schema');

 

3/. List All Views

SELECT table_name

FROM information_schema.views

WHERE table_schema NOT IN ('pg_catalog', 'information_schema')

AND table_name !~ '^pg_';

 

4/. List All Table Name

SELECT  table_name FROM information_schema.tables

WHERE table_type = 'BASE TABLE' AND table_schema NOT IN

('pg_catalog', 'information_schema')

 

5/. List Name of the field, data type of table.

// Suppose emp is table name

 

SELECT column_name, data_type

FROM information_schema.columns

WHERE table_name = 'emp';

 

6/. List Table Indexes

// Suppose emp is table name

 

SELECT  relname FROM pg_class

WHERE oid IN

    (SELECT indexrelid FROM pg_index, pg_class

       WHERE pg_class.relname='emp'

        AND pg_class.oid=pg_index.indrelid

        AND indisunique != 't' AND indisprimary != 't')

 

7/. List Table Constraints

SELECT  constraint_name, constraint_type FROM information_schema.table_constraints

WHERE table_name = 'emp' AND constraint_type!='CHECK'

 

8/. List Triggers

SELECT DISTINCT trigger_name FROM information_schema.triggers

WHERE event_object_table = 'emp'

AND trigger_schema NOT IN ('pg_catalog', 'information_schema')

 

9/. List Functions

SELECT routine_name FROM information_schema.routines

WHERE specific_schema NOT IN ('pg_catalog', 'information_schema')

 

10/. List Sequences

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