SQL Tips #6 - Looking for tables with specific fieldtypes

Friday, 16 September 2016

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.

 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


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


SQL, Programming
Share with: 

Useful SQL tips - how to a specific type of field in a SQL table

Support this Site

Popular Articles

What is Kutamo?
Kilimanjaro 2023
Kilimanjaro 2015
Kilimanjaro 2013
Australian Postcodes
New Zealand Postcodes
Worldwide City Database

Recent Articles

Favourite Links

Kutamo Studios
Kilimanjar 2023