How to find all tables containing column with specified name ?

To find all tables containing a column with a specified name, you can query the database’s metadata/system tables. Below are solutions for various database systems:

1. ANSI SQL (Works in MySQL, PostgreSQL, SQL Server, etc.)

SELECT 
    TABLE_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME = 'YourColumnName'; -- Replace with your column name

2. Microsoft SQL Server

SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    t.name AS TableName
FROM 
    sys.tables t
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
WHERE 
    c.name = 'YourColumnName';

3. MySQL/MariaDB

SELECT 
    TABLE_SCHEMA AS DatabaseName,
    TABLE_NAME AS TableName
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME = 'YourColumnName'

4. PostgreSQL

SELECT 
    table_schema AS SchemaName,
    table_name AS TableName
FROM 
    information_schema.columns
WHERE 
    column_name = 'your_column_name'; -- Case-sensitive in PostgreSQL

5. Oracle

SELECT 
    OWNER AS SchemaName,
    TABLE_NAME AS TableName
FROM 
    ALL_TAB_COLUMNS
WHERE 
    COLUMN_NAME = 'YOUR_COLUMN_NAME'; -- Use uppercase for Oracle

6. SQLite

SELECT 
    name AS TableName
FROM 
    sqlite_master
WHERE 
    type = 'table'
    AND sql LIKE '%YourColumnName%';

Key Notes

• Case Sensitivity:
• PostgreSQL: Column names are case-sensitive (use lowercase unless created with quotes).
• Oracle: Column names are stored in uppercase.
• Permissions: Ensure you have access to metadata/system tables.
• Wildcards: For partial matches, use LIKE (e.g., COLUMN_NAME LIKE '%Name%').

Bonus: Find Across All Databases (SQL Server)

DECLARE @ColumnName NVARCHAR(100) = 'YourColumnName';
DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = STRING_AGG(
    CONCAT(
        'SELECT ''', name, ''' AS DatabaseName, ',
        'SCHEMA_NAME(t.schema_id) AS SchemaName, ',
        't.name AS TableName ',
        'FROM ', name, '.sys.tables t ',
        'INNER JOIN ', name, '.sys.columns c ON t.object_id = c.object_id ',
        'WHERE c.name = ''', @ColumnName, ''''
    ), ' UNION ALL '
)
FROM sys.databases
WHERE state = 0; -- Only online databases

EXEC sp_executesql @SQL;

Tools

• SSMS (SQL Server): Use the “Find Column” feature in Object Explorer.
• pgAdmin (PostgreSQL): Search via the GUI schema browser.
• Third-Party Tools: Tools like DBeaver allow global column searches.

By querying system metadata tables, you can efficiently locate all tables containing a specific column across your database.