Query goal: | The table size is the sum of the total size of the simple columns and the total size of the complex columns in the table. In case of SQL databases large base tables in terms of number of columns could be a side effect of the problems with cloned columns or multiple columns for the same attribute. A base table with a low normalization level, which is meant to hold data that corresponds to multiple entity types has typically also relatively large number of columns compared with other base tables. Thus, the normalization level of base tables with a large number of columns should be checked as well. |
Notes about the query: | The query considers both base tables and derived tables. |
Query type: | Sofware measure (Numeric values (software measures) about the database) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH materialized_views AS (SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type, 1 AS weight FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid INNER JOIN pg_type ON pg_attribute.atttypid=pg_type.oid WHERE relkind = 'm' AND attnum>=1 AND typrelid=0 UNION SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type, (SELECT Count(*) AS cnt FROM pg_class AS c INNER JOIN pg_attribute AS a ON c.oid=a.attrelid WHERE c.oid=pg_type.typrelid) AS weight FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid INNER JOIN pg_type ON pg_attribute.atttypid=pg_type.oid WHERE relkind = 'm' AND attnum>=1 AND typrelid<>0), materialized_views_summary AS (SELECT nspname, relname, table_type, Sum(weight) AS ts FROM materialized_views GROUP BY nspname, relname, table_type), tables_with_no_columns AS (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)) SELECT A.table_schema, T.table_type, A.table_name, SUM (CASE WHEN A.data_type = 'USER-DEFINED' AND B.relnatts>=0 THEN B.relnatts ELSE 1 END) AS ts FROM information_schema.columns A LEFT JOIN ( SELECT n.nspname, c.relname, c.relnatts FROM pg_class c INNER JOIN pg_catalog.pg_authid u ON u.oid = c.relowner INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('c', 'r') AND (n.nspname = 'public' OR u.rolname <> 'postgres') ) B ON A.udt_name = B.relname AND A.udt_schema = B.nspname AND A.data_type = 'USER-DEFINED' INNER JOIN information_schema.tables T ON A.table_schema = T.table_schema AND A.table_name = T.table_name INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name WHERE (A.table_schema = 'public' OR S.schema_owner<>'postgres') GROUP BY A.table_schema, T.table_type, A.table_name UNION SELECT nspname, table_type, relname, ts FROM materialized_views_summary UNION SELECT table_schema, table_type, table_name, 0 AS ts FROM tables_with_no_columns ORDER BY table_type, ts DESC, table_schema, table_name; |
Collection name | Collection description |
---|---|
Find problems about base tables | A selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview |
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Find quick numeric overview of the database | Queries that return numeric values showing mostly the number of different types of database objects in the database |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Structure of base tables | Queries of this category provide information about the structuring of base tables at the database conceptual level |
Reference |
---|
Piattini, M., Calero, C., Sahraoui, H. A., & Lounis, H. (2001). Object-relational database metrics. L'Objet, 7(4), 477-496. |
Delplanque, J., Etien, A., Auverlot, O., Mens, T., Anquetil, N., Ducasse, S.: CodeCritics applied to database schema: Challenges and first results. In: 24th International Conference on Software Analysis, Evolution and Reengineering, pp. 432–436. IEEE, (2017). https://doi.org/10.1109/SANER.2017.7884648 (Too many columns in a table) |
Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55–64. ACM, (2018). https://doi.org/10.1145/3183519.3183529 (God table) |
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Creating tables as ‘God Objects’) |
Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345. (2020). https://doi.org/10.1145/3318464.3389754 (God Table) |