I want to show all tables that have specified column name

Diving into INFORMATION_SCHEMA

The INFORMATION_SCHEMA.COLUMNS is core to SQL databases, providing metadata about columns across tables. With it, you can access the schema information without dealing with the system tables directly — an elegant abstraction indeed.

Scaling up: handling duplicate names

SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'InsertColumnNameHere'
ORDER BY TABLE_SCHEMA, TABLE_NAME;


This query not only finds the tables but also sorts them by TABLE_SCHEMA and TABLE_NAME, ensuring unique entries even if tables with identical names exist in different schemas.

Cross-compatibility: the universal tool

This approach is database agnostic, covering MySQL, PostgreSQL, and SQL Server, among others. If you're a developer or DBA needing database audits, data model overviews, or preparing for database migration, this is your Swiss Army knife.

Database quirks: handling exceptions

For Oracle, replace INFORMATION_SCHEMA.COLUMNS with ALL_TAB_COLUMNS. For SQL Server, you might want to join sys.tables with sys.columns for an all-inclusive view.

Wildcard searching: the unknown unknowns

If you're not quite sure of the column name or want to find columns of similar names, here's how you do it:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'NamePrefix%';


Troubleshooting SQL queries

Let's delve a little deeper, considering exceptions to the rule and debugging tricky scenarios.

SQL, synonyms, and you

In some cases, you might deal with column synonyms or aliases. Remember, INFORMATION_SCHEMA does not include them. You'll need to check vendor-specific schema views or documentation.

The case for case sensitivity

SQL is a tricky animal and case sensitivity for column names can vary. So, play safe and use collations or case-handling functions:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE lower(COLUMN_NAME) = lower('InsertColumnNameHere');


Moral of the query - When in doubt, everything goes lower-case.

Reserved words: the escape maneuver

SQL can be pretty "reserved" sometimes. In certain cases, column names might coincide with reserved SQL keywords. Here's how to handle these:

SELECT `TABLE_NAME`  --MySQL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE `COLUMN_NAME` = `ReservedWord`;


In SQL, escape is not just a key on your keyboard.

Optimizing your treasure hunt : tips and tricks

• Indexing for performance: Large databases might make your searches slower. Check indexing on system tables for more efficient queries.
• Script automation: Frequent searches or multiple databases? Automate this with scripts in your favorite language.
• Scheduled inspections: Set up scripts for periodic schema reviews, because keeping up with evolving data structures is vital.