Created
October 4, 2012 15:09
-
-
Save msakrejda/3834234 to your computer and use it in GitHub Desktop.
Ballpark catalog state query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT n.nspname as "Schema", | |
c.relname as "Name", | |
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", | |
TEXT(c.reltuples) as "Info" | |
FROM pg_catalog.pg_class c | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
WHERE c.relkind IN ('r','') | |
AND n.nspname <> 'pg_catalog' | |
AND n.nspname <> 'information_schema' | |
AND n.nspname !~ '^pg_toast' | |
AND pg_catalog.pg_table_is_visible(c.oid) | |
UNION | |
SELECT n.nspname as "Schema", | |
c.relname as "Name", | |
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", | |
c2.relname as "Info" | |
FROM pg_catalog.pg_class c | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid | |
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid | |
WHERE c.relkind IN ('i','') | |
AND n.nspname <> 'pg_catalog' | |
AND n.nspname <> 'information_schema' | |
AND n.nspname !~ '^pg_toast' | |
AND pg_catalog.pg_table_is_visible(c.oid) | |
UNION | |
SELECT n.nspname as "Schema", | |
c.relname as "Name", | |
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", | |
x.conname as "Info" | |
FROM pg_catalog.pg_class c | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
LEFT JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid | |
WHERE c.relkind IN ('r','') | |
AND n.nspname <> 'pg_catalog' | |
AND n.nspname <> 'information_schema' | |
AND n.nspname !~ '^pg_toast' | |
AND pg_catalog.pg_table_is_visible(c.oid) | |
ORDER BY 1, 3 desc, 2, 4; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment