From time to time, you may want to list all your database tables that contain a specific field.
I recently had a need to find all datetime fields, to check that they were being updated correctly.
How it works
The query is very simple - it joins sys.sysobjects and sys.syscolumns and sys.systypes to form a combined recordset, and returns any that have a sys.systypes value that matches the specified type.
In this example, we're looking for any fields that are a datetime, date or time.
FROM dbo.sysobjects so
INNER JOIN syscolumns sc on (so.id = sc.id)
INNER JOIN systypes st on (st.type = sc.type)
WHERE so.type = 'U'
AND st.name IN ('DATETIME', 'DATE', 'TIME')
ORDER BY so.name
The query will return results similar to those below.
table_name column_name data_type
tblAccountManagers dateadded datetime
tblAnswerPoints dateadded datetime
tblCallingNumbers dateadded datetime
tblContacts dateadded datetime
tblContacts dateupdated datetime
These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.