SQL Tips #6 - Looking for tables with specific fieldtypes


Friday, 16 September 2016

Share with: 
 

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

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

Popular Articles

What is Kutamo?
Kilimanjaro 2015
Exploring Lync and IoT
Exchange 2013 in 60 minutes
Monitoring Lync with MRTG
Lync UCWA Tutorial - Introduction
Tutorial Parts 1 | 2 | 3 | 4 | 5

Recent Articles

Australian Postal Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo Studios
ErrLog.IO
Kutamo
Telco Together Foundation
Cloud on Kilimanjaro