Complete Guide: SQL LIKE Queries for Finding Tables with Columns

To find all database tables containing columns with a specific name using SQL LIKE queries, you can query the INFORMATION_SCHEMA.COLUMNS view with a WHERE clause that uses the LIKE operator with wildcards. This approach works across most database systems including MySQL, PostgreSQL, SQL Server, and Oracle, allowing you to search for partial column name matches efficiently.

Contents

• Basic INFORMATION_SCHEMA Query
• Database-Specific Approaches
• Using Wildcards with LIKE
• Case Sensitivity Considerations
• Performance Optimization
• Practical Examples

Basic INFORMATION_SCHEMA Query

The most universal method for finding tables containing columns with specific names uses the INFORMATION_SCHEMA.COLUMNS view, which is part of the SQL standard and available across most database systems. This approach allows you to search across your entire database schema without needing to know table names in advance.

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

For more precise filtering, you can add additional conditions:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG = 'YourDatabaseName'
  AND COLUMN_NAME LIKE '%your_search_term%'
  AND DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar');

As SQL Authority with Pinal Dave explains, the INFORMATION_SCHEMA method is very efficient for this purpose and works well across different database systems.

Database-Specific Approaches

While INFORMATION_SCHEMA works universally, some database systems offer alternative approaches that may provide better performance or additional functionality.

Microsoft SQL Server (sys.columns)

In SQL Server, you can query the system catalog views directly:

SELECT 
    c.name AS ColumnName, 
    t.name AS TableName
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%your_search_term%'
ORDER BY TableName, ColumnName;

According to GeeksforGeeks, this approach can be more efficient than using INFORMATION_SCHEMA in SQL Server environments.

PostgreSQL

PostgreSQL supports both INFORMATION_SCHEMA and its own system catalog:

SELECT 
    table_name, 
    column_name
FROM information_schema.columns
WHERE column_name ILIKE '%your_search_term%'; -- ILIKE for case-insensitive search

Or using system catalogs:

SELECT 
    c.relname AS TableName, 
    a.attname AS ColumnName
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE a.attname LIKE '%your_search_term%'
  AND c.relkind = 'r'; -- 'r' for regular tables

MySQL

In MySQL, you can use:

SELECT 
    TABLE_NAME, 
    COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND COLUMN_NAME LIKE '%your_search_term%';

Using Wildcards with LIKE

The LIKE operator supports several wildcard patterns that make it powerful for searching column names:

• % - Matches any sequence of zero or more characters
• _ - Matches any single character
• [] - Matches any single character within the specified range

Common Patterns

-- Find columns ending with 'id'
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%id';

-- Find columns starting with 'user'
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE 'user%';

-- Find columns with 'date' in the middle
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%date%';

-- Find columns with exactly 7 characters where 4th character is 'n'
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '___n___';

As ApexSQL demonstrates, these patterns are particularly useful when you have partial information about the column name structure.

Case Sensitivity Considerations

The behavior of LIKE with respect to case sensitivity varies across database systems:

Case-Sensitive Systems (Default)

• MySQL: Case-sensitive by default on Linux/Unix, case-insensitive on Windows
• PostgreSQL: Case-sensitive by default
• Oracle: Case-sensitive by default

-- Case-sensitive search
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE 'UserName%';

Case-Insensitive Options

Most systems offer case-insensitive alternatives:

• SQL Server: Use COLLATE or UPPER()/LOWER()
• PostgreSQL: Use ILIKE instead of LIKE
• MySQL: Use LOWER() or adjust collation

-- Case-insensitive approaches
-- SQL Server
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE LOWER(COLUMN_NAME) LIKE LOWER('%username%');

-- PostgreSQL (using ILIKE)
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME ILIKE '%username%';

As LearnSQL.com notes, understanding case sensitivity is crucial for getting accurate search results across different database systems.

Performance Optimization

When searching across large databases, performance considerations become important:

Indexing Considerations

The INFORMATION_SCHEMA.COLUMNS view typically doesn’t support indexing, so large table scans may be necessary. For frequently performed searches, consider:

-- Create a temporary table for repeated searches
CREATE TEMPORARY COLUMN_SEARCH_TABLES AS
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%user%';

-- Then query the temporary table
SELECT * FROM COLUMN_SEARCH_TABLES 
WHERE COLUMN_NAME LIKE '%admin%';

Limit Search Scope

Reduce the search space by adding additional filters:

-- Search only specific data types
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%email%'
  AND DATA_TYPE IN ('varchar', 'nvarchar', 'text');

-- Search only specific schemas
SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA IN ('dbo', 'public', 'schema1')
  AND COLUMN_NAME LIKE '%timestamp%';

Practical Examples

Finding User-Related Columns

-- Find all columns with 'user' in the name
SELECT 
    TABLE_CATALOG as DatabaseName,
    TABLE_SCHEMA as SchemaName,
    TABLE_NAME as TableName,
    COLUMN_NAME as ColumnName,
    DATA_TYPE as DataType
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%user%'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME;

Finding Email Address Columns

-- Find email-related columns
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%email%'
  AND DATA_TYPE LIKE '%char%'
ORDER BY TABLE_NAME, COLUMN_NAME;

Finding Date/Time Columns

-- Find date-related columns across all databases
SELECT 
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%date%'
  OR COLUMN_NAME LIKE '%time%'
  OR COLUMN_NAME LIKE '%created%'
  OR COLUMN_NAME LIKE '%modified%'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME;

These examples show how the LIKE operator can be combined with other conditions to create targeted searches for specific types of columns across your database schema.

Conclusion

• The INFORMATION_SCHEMA.COLUMNS approach is the most universal method for finding tables containing columns with specific names, supported across most database systems including MySQL, PostgreSQL, SQL Server, and Oracle.

• Wildcard patterns with LIKE (%, _, []) provide powerful search capabilities for partial column name matches, allowing you to find columns even when you only know part of their names.

• Database-specific optimizations exist - SQL Server’s sys.columns, PostgreSQL’s system catalogs, and MySQL’s INFORMATION_SCHEMA implementation may offer performance benefits in their respective environments.

• Case sensitivity varies by database system, so use appropriate functions (ILIKE in PostgreSQL, COLLATE in SQL Server, or UPPER()/LOWER() functions) when case-insensitive searches are needed.

• Performance considerations matter for large databases - limit searches by schema, data type, or create temporary tables for repeated searches to improve query execution times.

These techniques are essential for database administrators and developers who need to understand their database structure, perform impact analysis before schema changes, or locate specific data elements across complex database schemas.

Sources

1. SQL - Find all tables containing column with specified name - Stack Overflow
2. How to find all tables containing a column with the specified name in MS SQL Server - Sentry
3. SQL Tutorial: Identifying Tables within a Column - Atlassian
4. SQL - Find all Tables Containing Column with Specified Name - Intellipaat
5. Find all Tables that contain a specific Column name in SQL Server - GeeksforGeeks
6. Way to search all tables in a database for a specific column name - Reddit
7. SQL SERVER - Find All Tables Containing Specific Column Name - SQL Authority with Pinal Dave
8. How to write a query to find all tables in a db that have a specific column name - Database Administrators Stack Exchange
9. Finding Tables that Contain a Specific Column in SQL Server - Chartio
10. How to search for column names in SQL Server - ApexSQL
11. What Do the Operators LIKE and NOT LIKE Do? - LearnSQL.com
12. MySQL vs PostgreSQL: Choosing the Right Database for Your Project - DataCamp
13. SQL Server, PostgreSQL, MySQL: What’s the Difference? - DataCamp
14. SQL vs MySQL: Differences, Similarities, Uses, and Benefits - Coursera
15. Comparing SQL Database Technologies: MySQL, MSSQL, PostgreSQL, SQLite, and Oracle SQL - Medium
16. MYSQL vs PostgreSQL vs ORACLE - ByteScout
17. Difference between MySQL and PostgreSQL - GeeksforGeeks