Search a database for ALL invalid date records. 1

[Jamfool]

I was wondering is there anyway to return a columns type? e.g date,int,double etc etc.?

The reason I ask is because I need to search a database for date values that are below , and above a certain value or simply invalid. If it were only a few tables it wouldnt be a problem, but this database consists of over a hundred tables.
I would like to search thru the database listing the table and column which are set as a type date.

Then using the tablename&column do a check on them to see which records contain invalid data.

Problem:
1.First list all the tables
2.Get the columns & coresponding types
3. Select all the date columns and do a validity check

 

[swampBoogie]

According to the ANSI standard you can retrieve the data type for a column by using the following select statement

select * from information_schema.columns
 where data_type = 'DATE'

 

[Jamfool]

Thanks again swamp boogie your a star

Heres the code produced. if any one else needs to search database wide. Just use it to auto generate the search SQL
then run them.

	select 	'(select * from ' + 
		table_name + 
		' where ' + 
		column_name + 
		' <1/1/1900) print ''' + 
		table_name + 
		'/' + 
		column_name + 
		'''' + 
		char(10) 
		
	from 	information_schema.columns
	where	data_type in ('datetime')
	and	table_schema = 'dbo'