Select common field from inventory of tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select common field from inventory of tables

Author

Topic

iglesia777
Starting Member

6 Posts

Posted - 2007-01-17 : 14:03:38

Hello,

I am trying to select a common field from an inventory of tables I have.

tbl_Tables:
ID
TableName

tbl_Tables: EX
1,Table1
2,Table2
3,Table3

Each table has the field "DateEntered" that defaults to getdate() for each record added. What I want is the most recent date (dateentered) from each table.

Desired Result:
Table1, 1/1/2007
Table2, 12/1/2006
etc

I've tried the modify_date from sys.tables, but that doesn't give the specific data I want.

Should I be going about this a different way? Thanks a bunch.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 15:20:28

select tablename, max(dateentered) from tbl_tables
group by tablename

Peter Larsson
Helsingborg, Sweden

iglesia777
Starting Member

6 Posts

Posted - 2007-01-17 : 15:32:38

DateEntered is a field in Table1 and Table2. So it's not that easy.

I have a work-around using code to build a large SQL statement using UNION. But I'd like to do it in one SQL statement if possible.

EX SQL output:

Select MAX(DateEntered) FROM Table1
UNION
Select MAX(DateEntered) FROM Table2

But that doesn't let me sort it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 15:55:22

select max(dateentered)
from (
select dateentered from table1
union all
select dateentered from table1
) as x

Peter Larsson
Helsingborg, Sweden

iglesia777
Starting Member

6 Posts

Posted - 2007-01-17 : 17:15:01

That would work if I had a set number of tables (Table1, Table2, etc). But tables are constantly being added. So I'm keeping an inventory of them in the "tbl_Tables" table. Otherwise I'd have to update the SQL every time another table was added.

So I need to iterate through "tbl_Tables" to get all the table names first. Then UNION them all together. Make sense?

Thanks for sticking with me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-17 : 17:17:12