February 2, 2011 at 11:12 pm
hi,
i am using SQL Server 2005 DB for our APP. There are about 125 tables in the database. Want to get a list of tables updated in the last one month without opening each and every table.
For example,
Table 1 was last updated on 01/01/2011
Table 2 was last updated on 01/02/2011
Table 3 was last updated on 12/01/2010
Table 4 was last updated on 12/25/2010
want to have a query to pull the tables which have been updated since Jan 1st, 2011. In this case, it should return only the first two tables. Forget about the contents inside the table.
I can always open each and every table to get a row level timestamp and filter it accordingly but it is time consuming.
thanks.
February 3, 2011 at 2:46 am
Unfortunately there's no built-in "last updated" property for table data, you will have to calculate it from table data itself.
You could create a dynamic sql to check all the tables at once, something similar to this:
IF OBJECT_ID('tempdb..#timestamps') IS NOT NULL
DROP TABLE #timestamps
CREATE TABLE #timestamps (
table_name nvarchar(255),
last_updated datetime
)
DECLARE @sql nvarchar(max)
DECLARE @timestamp_column varchar(128)
DECLARE @last_updated_min datetime
--INSERT YOUR "TimeStamp" COLUMN NAME HERE
SET @timestamp_column = 'last_updated'
--INSERT YOUR FILTER HERE
SET @last_updated_min = '20110203'
SET @sql =
STUFF((
SELECT ';INSERT INTO #timestamps SELECT ''' + QUOTENAME(COLS.TABLE_SCHEMA) + '.' + QUOTENAME(COLS.TABLE_NAME) + ''', MAX('+@timestamp_column+') FROM ' + QUOTENAME(COLS.TABLE_SCHEMA) + '.' + QUOTENAME(COLS.TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLES AS TABS
ON TABS.TABLE_CATALOG = COLS.TABLE_CATALOG
AND TABS.TABLE_SCHEMA = COLS.TABLE_SCHEMA
AND TABS.TABLE_NAME = COLS.TABLE_NAME
WHERE COLS.COLUMN_NAME = @timestamp_column
AND TABS.TABLE_TYPE = 'BASE TABLE'
FOR XML PATH('')
), 1, 1, SPACE(0));
--PRINT @sql
EXEC(@sql)
SELECT *
FROM #timestamps
WHERE last_updated >= @last_updated_min
ORDER BY last_updated DESC
Hope this helps.
Gianluca
-- Gianluca Sartori
February 3, 2011 at 10:03 am
With 2005 and later we now have the DMVs that can be used for this. Take a look at the sys.dm_db_index_usage_stats view. It has the last_user_scan,...,last_user_update columns. One thing to remember is that this table is only capturing data since the last restart so you may want to build a nightly process like me to hold the max date of the columns as a last_user_touch field somewhere for each of the tables.
February 3, 2011 at 10:11 am
DBA_Dom (2/3/2011)
With 2005 and later we now have the DMVs that can be used for this. Take a look at the sys.dm_db_index_usage_stats view. It has the last_user_scan,...,last_user_update columns. One thing to remember is that this table is only capturing data since the last restart so you may want to build a nightly process like me to hold the max date of the columns as a last_user_touch field somewhere for each of the tables.
Dom, I was about to suggest this, but I decided it would have been confusing for the op.
As you mentioned, dm_db_index_usage_stats holds data since last server start. Moreover, if you rebuild the indexes, the record for the index gets reset.
I would not recommend using this technique, unless a certain degree of approximation is allowed.
-- Gianluca Sartori
February 3, 2011 at 10:45 am
Granted there is a margin of error for rebuilding indexes but that can be taken into account when the process runs with different types of maintenance windows (i.e. audits are done before full/diff backups and index rebuilds) if the basic window is large enough or things are tuned well enough to be very quick. I use this to also track selects that are issued against the tables.
Gianluca's idea is good if all the tables needed do contain some type of update field.
February 3, 2011 at 6:33 pm
Thanks for your reply. when i ran this query by setting a new value to @last_updated_min, i didn't get any results back and no error message showed up. The print SQL statement also didn't get execute. Looks like there is a issue in the query. Can you please revisit this again?
February 4, 2011 at 12:58 am
jamesmario (2/3/2011)
Thanks for your reply. when i ran this query by setting a new value to @last_updated_min, i didn't get any results back and no error message showed up. The print SQL statement also didn't get execute. Looks like there is a issue in the query. Can you please revisit this again?
Probably some NULL value got in the way and set the whole @sql string to NULL.
Can you test this in your environment?
-- Gianluca Sartori
February 4, 2011 at 6:31 am
yes, the sql works fine now. There was a typo in the value assigned to the column name '@timestamp_column'. great work.
February 4, 2011 at 6:39 am
Glad I could help.
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply