The following post shows a simple query to find a specified field in a given database, using system views sys.all_columns and sys.all_objects in a specified database on your SQL Server. This query provides a quick way to search your database for a field to see any objects that may be impacted if you intent to make any schema changes. The example is built using a LIKE search, where the search string is declared in a variable.
SQL Find Field in Database – TSQL
Here is the TSQL code, which you can run against any specified database. For this example, I am using the AdventureWorks2012 database
DECLARE @Name VARCHAR(50)
DECLARE @SearchName VARCHAR(50)
SET @Name = 'Email'
SET @SearchName = ('%' + @Name + '%')
@SearchName as FieldSearchString
WHERE object_id IN ( SELECT object_id
WHERE name like @SearchName )
I hope you find this TSQL code useful.