June 28, 2011 at 11:37 am
i am trying to figure a way to return data that includes the oldest record in each table for a specific DB. i pulled the record counts per each table, but not sure how i can either tie the actual oldest record to that query, or write another one that just gives me the oldest record in each table. (trying to eliminate having to retype the table names 117 times)
Example;
Database = PRODDATA
Tables
tb1
tb2
tb3
tb4
tb5
tb6
each table will have a createdate, therefore i would like a way to return all records for PRODDATA DB that the createdate in each of the tables are older than 12/31/2010.....
June 28, 2011 at 12:16 pm
OK, so to get a list of tables that are older than that date, this works:
SELECT name
FROM sys.objects
WHERE type = 'u'
AND create_date < '12/31/2010'
However that is the name of the tables. Returning all the data in those tables doesn't usually make sense. Note that the creation date doesn't mean you had data in those tables as of that date. Data could be added later.
Returning all data from all tables matching some filter doesn't make sense either. Tables have different structures, so you can't necessarily combine those result sets.
Can you elaborate on what you need to do, rather than what you want to do here. I think you may be approaching those problem slightly incorrectly.
June 28, 2011 at 12:18 pm
Thank you...but its not the actual TABLE(s), its the records in the tables i am trying to return the oldest record (date) in each table.
June 28, 2011 at 12:25 pm
In AdventureWorks, this will return the oldest record:
SELECT cc.CreditCardID, cc.CardNumber, cc.ModifiedDate
FROM Sales.CreditCard cc
WHERE cc.ModifiedDate = (SELECT MIN(ModifiedDate)
FROM Sales.CreditCard
)
The problem for doing this with a large number of tables is that unless you have some standard field in each table, like modified_date, you can't easily write a script. There is no meta data that tells SQL what field in the table actually corresponds to the age of a row.
June 28, 2011 at 2:02 pm
Do these tables have an 'inserted date' column or identity column? If not, there's no way to get insert/modification dates. SQL does not keep track of such things for rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply