Hi, is there someone out there who can help me select multiple records that were entered/updated on different tables on the same time in the SQL Server database. Do you think this is possible or not?
Your help is highly appreciated, thanks!
views:
61answers:
4
+1
A:
Does your table have a date/time-stamp column of type DATETIME??
Otherwise you're probably out of luck....
Marc
marc_s
2009-05-07 08:48:03
the tables have date/time-stamp, but what I need is how would I know all the records that were entered? or updated?
KG Sosa
2009-05-07 08:53:32
Well, there's really not much you can do other than selecting from all potential tables, with a "WHERE CreatedDate = (date of interest)" and then see where you get results. There's no magic way to select from all tables for a particular date...
marc_s
2009-05-07 09:34:41
A:
Hm... could it be something as simple as
SELECT *
FROM MyTable
WHERE CreatedDate = 'the date you are interested in'
or
SELECT *
FROM MyTable
GROUP BY CreatedDate
HAVING COUNT(*) > 1
?
Tomalak
2009-05-07 08:55:26
the case is, there are alot of tables, how would I know which table/tables are updated?
KG Sosa
2009-05-07 08:57:36
A:
if you are inside the insert trigger, you can query the inserted pseudo-table.
Arjan Einbu
2009-05-07 09:02:38
A:
If the DATETIME columns are all named the same, you can generate the queries like
select
'select * from '
+ t.name
+ ' where UpdateDate = ''2009-05-01''' as SelectStatement
from sys.tables as t
Or if the DATETIME column name is not static, you can do something along these lines:
select
'select * from ' + t.name
+ ' where ' + c.name + ' = ''2009-05-01''' as SelectStatement
from sys.tables as t join sys.columns as c on t.object_id=c.object_id
where c.system_type_id = (select system_type_id from sys.types where name = 'datetime')