sql server misc: Im a newby to SQL so forgive me if this is a stupid question.
If I make an entry in my application can I query the database to return any
entries in any table that has been updated by the entry in the application?
I know I can return from a single table by using the statement select * from
TABLE_X where create_dttm = 09/07/2004 but can I get it to return the
entries from all tables instead of just TABLE_X?

Thanks

Hi DP. Nice initials. I'll take a stab at this. Someone correct me if I'm
wrong..

I do not believe you can accomplish what you want, getting a select table to
operate on multiple tables unless you do it through multiple selects,
perhaps appending them into a temporary table, or some sort of join or union
query to select from all the tables at once, having to list all of them, and
how they relate to the other tables.

One thing that seems to stick out from what you said your post though, is
that why do you want to search through multiple tables to find a value like
that? Do all these tables contain the same field definition 'create_dttm'?
Are the rest of the fields in the tables the same? If so, that sounds like
a big normalization no-no. All of this data from these separate tables
ought to be concatenated into one large table that has all the same field
definitions, except also has an additional key identifier field, lets call
it tableOrigin, and populate it with the original table which you want to
get the data from, the individual table names you're wishing to search
though. In the end you'd have one huge table with all of the data from all
the individual tables in it. It's a real good idea to have another primary
key defined for this table as well. Next you'd use a select query like
this:

Select * from tblOrders where create_dttm = 09/07/2004

or more to the point,

Select TableOrigin, create_dttm from tblOrders where create_dttm =
09/07/2004 order by TableOrigin;

This reflects the way I use SQL to get the most out of my data. I have
situations similar to the one you described in your original post. I spent
hours writing routines to search out disparate tables, trying to piece
together this field and that field. I couldn't join all the tables together
because the keys weren't properly defined, or of the same data type between
these tables, and other reasons exist. I find in the end though, that if I
take this larger approach, where I spend the extra time ahead of time trying
to normalize my data, then I spend a lot less time during report
creation/front end creation time trying to piece together all these data
scattered to and fro.

-BrianDP


[quoted text, click to view]

[quoted text, click to view]
Thanks - the reason is that I want to find out which tables have been updated by
making an entry into the application, but from the sound of it I'll have to do
it the hard way!