| Query goal: | Do not have in a database elements that are not useful. PostgreSQL permits tables with no columns. Such tables can be used to implement Boolean variables (tables TABLE_DEE and TABLE_DUM). On the other hand, such tables might be a result of database evolution, where developers have not noticed that they have dropped all the columns of a table or have not noticed that they have created such a table in the first place. |
| Notes about the query: | The query considers only columns that are visible to the database user and not hidden system columns. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | Low (Many false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | If the table was not created intentionally, then add a column or drop the table. |
| Data source: | INFORMATION_SCHEMA+system catalog |
| SQL query: | Click on query to copy it
SELECT table_schema, table_name, table_type
FROM INFORMATION_SCHEMA.tables AS t
WHERE NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.columns AS c
WHERE t.table_schema=c.table_schema
AND t.table_name=c.table_name)
UNION SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
WHERE NOT EXISTS (SELECT 1
FROM pg_attribute AS a
WHERE c.oid=a.attrelid)
ORDER BY table_schema, table_name, table_type; |