Logo
Back

Is it possible to query for table names which contain columns being LIKE '%myName%'?

Yes! You can query your database’s system catalog (or information schema) to find any tables that have columns whose name matches a given pattern. Although each SQL engine (SQL Server, MySQL, PostgreSQL, Oracle, etc.) uses a different system catalog structure, the idea remains the same. Below, we’ll explore how to do this in SQL Server, MySQL, and PostgreSQL.

1. SQL Server

In Microsoft SQL Server, you can use the sys.columns and sys.tables system views:

SELECT 
    t.name AS TableName,
    c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t 
    ON c.object_id = t.object_id
WHERE c.name LIKE '%myName%'
ORDER BY t.name, c.name;
  • sys.columns: Holds metadata about columns.
  • sys.tables: Holds metadata about tables.
  • WHERE c.name LIKE '%myName%': Filters columns whose name matches the specified pattern.
  • ORDER BY: For a neat listing.

Caveats

  • Make sure you’re in the correct database context (USE YourDatabase;).
  • You need the appropriate permissions to view system tables.

2. MySQL

For MySQL, you’ll use the Information Schema:

SELECT 
    TABLE_NAME AS TableName,
    COLUMN_NAME AS ColumnName
FROM information_schema.columns
WHERE column_name LIKE '%myName%'
    AND table_schema = 'YourDatabaseName'
ORDER BY TABLE_NAME, COLUMN_NAME;
  • information_schema.columns: Contains column metadata for all databases on your MySQL server.
  • table_schema = 'YourDatabaseName': Restricts the query to a specific database (schema).

3. PostgreSQL

In PostgreSQL, you can query information_schema.columns similarly to MySQL:

SELECT 
    table_name AS TableName,
    column_name AS ColumnName
FROM information_schema.columns
WHERE column_name LIKE '%myName%'
    AND table_catalog = 'YourDatabaseName'
    -- AND table_schema = 'public'  -- Optional, if you only want a specific schema
ORDER BY table_name, column_name;
  • table_catalog: The database name.
  • table_schema: The schema name (e.g., public, sales, etc.).

4. Other Databases

  • Oracle: You’d typically query the ALL_TAB_COLUMNS or USER_TAB_COLUMNS view, filtering by COLUMN_NAME.
  • SQLite: SQLite doesn’t have a robust information schema, but you can use PRAGMA table_info(table_name) for each table. For a large schema, you might need external tooling.

5. Performance and Practical Tips

  1. Filtering
    • Narrow your query to a specific database/schema to avoid scanning unnecessary metadata.
  2. Permissions
    • Ensure your user account has rights to read the system catalog or information schema.
  3. Exact Matching
    • If you need an exact column name match, use = 'myName' instead of LIKE '%myName%'.
  4. Case Sensitivity
    • Depending on your database settings, column names may be case-sensitive or case-insensitive.

6. Level Up Your Database Knowledge

If you want to advance your database skills—beyond finding columns by name—check out these courses from DesignGurus.io:

7. Ready for Mock Interviews or a Comprehensive BootCamp?

  • Mock Interviews: Get personalized feedback from ex-FAANG engineers on coding, system design, and database-related questions.
  • Interview BootCamp: Prefer a guided schedule? Try the Interview BootCamp to cover everything from coding to system design and behavioral prep.
  • DesignGurus YouTube Channel: Check out DesignGurus on YouTube for free tutorials, Q&As, and tips on interviews and system design.

Conclusion

Querying for table names that contain specific columns—where the column names match a pattern—is straightforward once you know how to access your database’s system catalog or information schema. Whether you’re on SQL Server, MySQL, PostgreSQL, or another platform, the approach is similar: filter metadata tables with a LIKE condition.

For deeper expertise in databases and to prepare for high-stakes interviews, explore DesignGurus.io for courses and mock interview services that will level up your skills. Happy querying!

CONTRIBUTOR
TechGrind