How to Find Tables with Specific Column Names

To find tables containing columns with a specified pattern like ‘%myName%’, you can use SQL queries that leverage system views and metadata tables. The most common approach is querying the INFORMATION_SCHEMA.COLUMNS view or database-specific system tables like sys.columns in SQL Server, using the LIKE operator with wildcards to match partial column names. This works across multiple database systems with slight syntax variations.

Contents

• SQL Server Solutions
• Cross-Database Standard Approach
• Oracle Database Solution
• MySQL and PostgreSQL Variations
• Performance Optimization Tips
• Practical Examples

SQL Server Solutions

SQL Server offers multiple ways to find tables with columns matching a specified pattern. The most straightforward approach uses the INFORMATION_SCHEMA.COLUMNS view:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%myName%';

For more detailed results including schema information, you can use the system tables:

SELECT t.name AS table_name, 
       SCHEMA_NAME(schema_id) AS schema_name, 
       c.name AS column_name
FROM sys.tables AS t 
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
WHERE c.name LIKE '%myName%'
ORDER BY schema_name, table_name;

According to IntelliPaat’s guide, the sys.tables and sys.columns approach typically performs better than INFORMATION_SCHEMA for metadata queries in SQL Server, though both are effective for finding column patterns.

Cross-Database Standard Approach

The INFORMATION_SCHEMA.COLUMNS view provides a standardized way to query column metadata across different database systems that support SQL standards:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%';

This approach works consistently across:

• SQL Server
• MySQL
• PostgreSQL
• Other ANSI SQL-compliant databases

As mentioned in the Atlassian database guide, this method provides a simple way to look up all tables and associated columns with a particular or similar column name pattern.

Oracle Database Solution

Oracle uses its own system tables for metadata queries. Here’s how to find tables with columns matching your pattern:

SELECT t.owner AS schema_name, 
       t.table_name
FROM sys.dba_tab_columns col 
INNER JOIN sys.dba_tables t ON col.owner = t.owner 
                           AND col.table_name = t.table_name
WHERE col.column_name LIKE '%myName%'
-- Exclude system schemas
AND col.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'DBSNMP', 
                      'EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW', 
                      'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN',
                      'SI_INFORMTN_SCHEMA', 'WKSYS', 'WMSYS', 'XDB')
ORDER BY col.owner, col.table_name;

The Oracle Data Dictionary Queries resource emphasizes the importance of excluding Oracle-maintained schemas to avoid irrelevant system tables in your results.

MySQL and PostgreSQL Variations

MySQL supports both INFORMATION_SCHEMA and its own system tables:

-- Using INFORMATION_SCHEMA (standard)
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%';

-- MySQL alternative using information_schema
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%';

PostgreSQL offers similar approaches with some PostgreSQL-specific features:

-- Standard approach
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%myName%';

-- PostgreSQL-specific with pg_catalog
SELECT n.nspname as schema_name,
       c.relname as table_name,
       a.attname as column_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE a.attname LIKE '%myName%'
AND NOT a.attisdropped
AND c.relkind = 'r';

Performance Optimization Tips

When querying metadata tables for column patterns, consider these performance strategies:

1. Use system tables for better performance: As noted in the IntelliPaat guide, sys.columns and sys.tables typically perform better than INFORMATION_SCHEMA for metadata queries in SQL Server.

2. Limit the scope: Specify a database or schema when possible to reduce the search space:

   sql

   -- SQL Server
   SELECT TABLE_NAME, COLUMN_NAME 
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_CATALOG = 'YourDatabaseName'
   AND COLUMN_NAME LIKE '%myName%';
   

3. Consider indexing: Some databases allow indexing on metadata tables for faster lookups.

4. Cache results: Since schema information doesn’t change frequently, consider caching the results of metadata queries.

Practical Examples

Here are some practical examples for different scenarios:

Finding all tables with columns ending in ‘ID’:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ID%';

Finding tables with columns containing ‘metadata’ (case-insensitive):

-- SQL Server
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LOWER(COLUMN_NAME) LIKE '%metadata%';

-- PostgreSQL
SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name ILIKE '%metadata%';

Getting count of matching columns per table:

SELECT TABLE_NAME, COUNT(*) as column_count
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%'
GROUP BY TABLE_NAME
ORDER BY column_count DESC;

As the Stack Overflow community demonstrates, these queries are “simple as that” and widely used by developers to find all instances of column names in a given database schema.

Sources

1. SQL - Find all Tables Containing Column with Specified Name - IntelliPaat
2. How to find all tables containing a column with the specified name in MS SQL Server - Sentry
3. Find tables with a specific column name in Oracle database - Dataedo
4. SQL Tutorial: Identifying Tables within a Column - Atlassian
5. SQL - Find all tables containing column with specified name - Stack Overflow
6. How to write a query to find all tables in a db that have a specific column name - Database Administrators Stack Exchange

Conclusion

Finding tables with columns matching a specified pattern like ‘%myName%’ is a common database administration task that can be accomplished using several approaches. The INFORMATION_SCHEMA.COLUMNS view provides a standardized method across different SQL databases, while system-specific tables like sys.columns in SQL Server and dba_tab_columns in Oracle offer more detailed metadata with potentially better performance. Always consider filtering out system schemas and databases when working in production environments to get relevant results. For complex databases, caching metadata query results or using specialized tools like Devart can improve efficiency.