Querying Database Tables by Column Name

Querying database tables by column name is a common task that can be useful for various purposes, such as data analysis, database maintenance, and troubleshooting. In this tutorial, we will explore how to query database tables based on the presence of columns with specific names.

Introduction to System Views

Most databases provide system views that contain metadata about the database structure, including table and column information. These system views can be used to query the database for tables containing columns with specific names.

In SQL Server, the sys.columns and sys.tables system views are commonly used for this purpose. The INFORMATION_SCHEMA.COLUMNS view is another option that provides a standardized way of accessing column metadata across different databases.

Querying Tables by Column Name

To query tables by column name, you can use the following SQL syntax:

SELECT 
    c.name AS 'ColumnName',
    (SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    c.name LIKE '%MyName%'
ORDER BY 
    TableName, ColumnName;

This query joins the sys.columns and sys.tables views based on the object_id column and filters the results to include only columns with names containing the string ‘MyName’.

Alternatively, you can use the INFORMATION_SCHEMA.COLUMNS view:

SELECT 
    COLUMN_NAME AS 'ColumnName',
    TABLE_NAME AS 'TableName'
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME LIKE '%MyName%'
ORDER BY 
    TableName, ColumnName;

This query provides a similar result set to the previous example but uses the INFORMATION_SCHEMA.COLUMNS view instead.

Querying Tables and Views by Column Name

To include views in your search results, you can modify the previous queries to join with the sys.views system view:

SELECT 
    c.name AS 'ColumnName',
    (SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
UNION ALL
SELECT 
    c.name AS 'ColumnName',
    (SCHEMA_NAME(v.schema_id) + '.' + v.name) AS 'TableName'
FROM 
    sys.columns c
JOIN 
    sys.views v ON c.object_id = v.object_id
WHERE 
    c.name LIKE '%MyName%'
ORDER BY 
    TableName, ColumnName;

This query combines the results from the sys.tables and sys.views system views to include both tables and views in the search results.

Customizing Your Query

You can customize your query to include additional columns or filter the results based on specific conditions. For example:

SELECT 
    s.name AS 'Schema',
    t.name AS 'Table',
    c.name AS 'Column',
    d.name AS 'Data Type',
    c.max_length AS 'Length'
FROM 
    sys.schemas s
INNER JOIN 
    sys.tables t ON s.schema_id = t.schema_id
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
INNER JOIN 
    sys.types d ON c.user_type_id = d.user_type_id
WHERE 
    c.name LIKE '%MyName%'
AND 
    c.is_nullable = 0;

This query includes additional columns, such as the schema name, data type, and length of the column, and filters the results to exclude nullable columns.

Conclusion

Querying database tables by column name is a powerful technique that can help you analyze and maintain your database. By using system views and customizing your queries, you can efficiently search for tables containing specific columns and gain valuable insights into your database structure.