Query multiple database tables

Sql2005? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?

If you are doing this in SSIS then look up property expressions in Books Online. You can use this to dynamically build the SELECT statement, generating the table name based on a date, or today's date - 1.

Having tables in this way seems a bit strange, certainly at such a low level of day. You may want to investigate partitioned views, or partitioned tables. These concepts present a uniform view a to something like a SELECT statement, but you can still organise the data into tables or partitions respectively.