detect existence of datetime fields

[JTWhaler]

I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)

Help greatly appreciated..

[mountain man]

"JTWhaler" <jo***************@yahoo.com> wrote in message
news:ed*************************@posting.google.co m...

I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)

Help greatly appreciated..

Here is a script that does a reasonable task of generating
a useful data dictionary out of any database.

To identify the datetime fields simply add
a restriction line in the WHERE bit .... such as
and t.name in ('DateTime', 'SmallDateTime')
select substring(o.name,1,50) as "Table Name",
o.type "Typ",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length

from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)
where o.type = 'U'
order by 1,3

[Mystery Man]

jo***************@yahoo.com (JTWhaler) wrote in message news:<ed*************************@posting.google.c om>...

I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)

Help greatly appreciated..

Hi Jonah

The attached T-sql will create a database table that contains an entry
for each datetime column in the database

drop table datetime_column

create table datetime_column
(
table_name varchar(200)
,column_name varchar(200)
,datetime_value datetime null
)
declare @table_schema as varchar(200)
declare @table_name as varchar(200)
declare @column_name as varchar(200)
declare @query as nvarchar(1000)

DECLARE column_cursor CURSOR FOR
SELECT
table_name, column_name
FROM
INFORMATION_Schema.columns
where
data_type = 'datetime'
and table_name != 'datetime_column'

open column_cursor

FETCH NEXT FROM
column_cursor
INTO
@table_name, @column_name

while @@fetch_status = 0
begin
select @query = ' insert into datetime_column select ' + '''' +
@table_name + '''' + ',' + '''' + @column_name + '''' + ',' +
@column_name + ' from ' + @table_name
--select @query
exec sp_executesql @query

FETCH NEXT FROM
column_cursor
INTO
@table_name, @column_name
end

close column_cursor
deallocate column_cursor
select * from datetime_column