SQL Tips #6 - Looking for tables with specific fieldtypes

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.

The Query

In this example, we're looking for any fields that are a datetime, date or time.

SELECT
 so.name table_name
 ,sc.name column_name
 ,st.name data_type
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 Output

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

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