Query goal: | Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these. |
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: | Rename the column or domain. Use a prefix in the domain name. |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT table_schema, table_name, column_name, data_type, domain_name, domain_schema FROM INFORMATION_SCHEMA.columns WHERE (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND (column_name ILIKE domain_name OR column_name ILIKE data_type) AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) ORDER BY table_schema, table_name, ordinal_position; |
Category name | Category description |
---|---|
Domains | Queries of this category provide information about reusable specifications of column properties. |
Naming | Queries of this category provide information about the style of naming. |