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.
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 query will return the matching column name and table in which its contained.
These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.