Query to find all the tables with date columns in it

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

Thanks

From stackoverflow

• 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
  

• 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')
  

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

  Learning : +1 for working in Sybase too. Nice!

  caseyboardman : Easily modified to find all tables with a specific column name as well. (Which is how I got here.) Thanks!