PostgreSQL System Table Queries

system-queries.sql

// This query returns a list of tables, in alphabetical order, with a count of the columns.

SELECT  table_name
       ,COUNT(column_name)
FROM information_schema.columns
WHERE table_schema = 'myschema' -- put your schema here 
GROUP BY  table_name
ORDER BY table_name;

// This query returns a list of tables, in alphabetical order, with a count of the rows.

SELECT  schemaname
       ,relname
       ,n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'myschema'
ORDER BY relname;

// This query returns a list of tables, in alphabetical order, with their column names, data types and lengths.

SELECT  table_schema
       ,table_name
       ,column_name
       ,data_type
FROM information_schema.columns
WHERE table_schema = 'myschema'; 

// This query returns a list of column names that match the search criteria in the WHERE clause.

SELECT  column_name
       ,table_name
FROM information_schema.columns
WHERE column_name = 'mycolumn' --put your column name here 
AND table_schema = 'myschema'; -- put your schema here 

// This query returns a list of tables, in alphabetical order, from the schema or database requested.

SELECT  table_name
FROM information_schema.tables
WHERE table_schema = 'myschema' -- put your schema here
ORDER BY table_name;

// This query returns a list of tables, in alphabetical order, with their last modified and created date.

SELECT  schemaname
       ,COUNT(tablename)
FROM pg_tables
GROUP BY  schemaname
ORDER BY schemaname;