471,090 Members | 1,294 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

detect existence of datetime fields

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..
Jul 20 '05 #1
2 2790
"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

Jul 20 '05 #2
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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sorisio, Chris | last post: by
reply views Thread by fowlertrainer | last post: by
7 posts views Thread by Eddy | last post: by
20 posts views Thread by FAQ server | last post: by
5 posts views Thread by iulian.ilea | last post: by
7 posts views Thread by TheLostLeaf | last post: by
7 posts views Thread by billygotee | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.