Query to find all the tables with date columns in it

Question

Can anybody let me know the query to find all the tables that has a date columns on it.

Thanks

Answer 1

This should do the trick, just add in additional types if you need them:

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from 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', 'SMALLDATETIME')

edit: this also works in Sybase and any other T-SQL variants.

** Update 2/3/2025 **

the above does not work in MS SQL newer databases but the below does

SELECT so.name table_name
  ,sc.name column_name
  ,st.name data_type
FROM sys.objects so
INNER JOIN sys.columns sc ON so.object_id = sc.object_id
INNER JOIN sys.types st ON sc.user_type_id = st.user_type_id
WHERE so.type = 'U'
  AND st.name IN ('DATETIME', 'DATE', 'TIME', 'SMALLDATETIME')