Goal This query identifies base table columns designated for storing codes (e.g., vin and isbn codes) that lack appropriate length constraints reflecting real-world data requirements. It operates on a heuristic basis, targeting columns whose identifiers imply code data (e.g., names containing "isbn" or "vin") but whose definitions fail to account for standard maximum lengths. This includes both insufficient allocation (truncation risk) and unbounded allocation (data quality risk). Ensuring these fields are sized according to domain standards is crucial for data integrity and usability.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH codes AS (SELECT table_schema, table_name, column_name,  data_type, character_maximum_length,
CASE WHEN column_name~*'(vin.*(code|kood))' AND character_maximum_length<>17 THEN 'A standard Vehicle Identification Number (VIN) has a maximum and fixed length of 17 characters.'
WHEN column_name~*'(isbn)' AND character_maximum_length NOT IN (13,17) THEN 'The maximum length of an ISBN (International Standard Book Number) is 13 digits. A standard 13-digit ISBN (ISBN-13) has four hyphens, which divide the number into five distinct parts. Old ISBNs (known as ISBN-10) are exactly 10 digits long. A 10-digit ISBN (ISBN-10) typically contains three hyphens when properly formatted'
ELSE 'other' END AS mistake
FROM INFORMATION_SCHEMA.columns
WHERE data_type~*'char' 
AND column_name~*'((vin.*(code|kood))|isbn)')
SELECT table_schema, table_name, column_name,  data_type, character_maximum_length, mistake
FROM codes
WHERE mistake<>'other'
ORDER BY mistake, character_maximum_length, table_schema, table_name;

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Field size

Queries of this category provide information about the maximum size of values that can be recorded in column fields

Result quality depends on names

Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.

NameDescription
Field sizeQueries of this category provide information about the maximum size of values that can be recorded in column fields
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.