Query to find all the tables with date columns in it

Question

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

Thanks

Answer 1

This uses INFORMATION_SCHEMA on tables in the current database.
Tested in SQL Server 2008.

    select distinct c.TABLE_NAME     
    from INFORMATION_SCHEMA.COLUMNS as c
    where c.DATA_TYPE = 'datetime'

Another query for SQL 2005/2008 using system views:

select tbl.name as 'Table', c.name as 'Column Name', t.name as 'Type'
from sys.columns as c
inner join sys.tables as tbl
on tbl.object_id = c.object_id
inner join sys.types as t
on c.system_type_id = t.system_type_id
where t.name in ('datetime', 'date')
order by tbl.name