SQL Tips #2 - Searching for a specific field name in a SQL table

Ever wanted to find out which of your dozens of tables contains a certain fieldname or part of a fieldname? I did recently, as I needed to rename a field that was incorrectly named due to lack of coffee.

How it works

The query is very simple - it just searches the sys.tables collection for a column name that contains a string

The query looks joins the sys.columns< collection onto sys.tables, so that it can search a recordset containing all tables and their columns.

We're also aliasing the table name from t.name and column name from c.name to simplify the query.

The Query

This example query looks for any table that contains a field with the word userid in it.

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%userid%' order by tablename

The Output

The query will return the matching column name and table in which its contained.

userid	tblAccountManagers
userid	tblUsers

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags