July 9, 2014 at 12:01 am
How to get recently updated tables with total row count in sql server 2008?
Thanks,
Shiva N
Database Consultant
July 9, 2014 at 8:48 am
shiva N (7/9/2014)
How to get recently updated tables with total row count in sql server 2008?
You would have had to have had some kind of change tracking in place previously.
July 9, 2014 at 9:15 am
This will give you table name and rows:
SELECT sysobjects.name
, sysindexes.Rows
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
type = 'U'
AND sysindexes.IndId < 2
If your table doesn't have a field with a date/timestamp I think you are out of luck on the last updated part.
July 9, 2014 at 10:51 am
This might work:
SELECT
OBJECT_NAME(P.object_id) AS tableName,
SUM(P.rows),
DDIUS.last_user_update,
DDIUS.last_system_update
FROM
sys.dm_db_index_usage_stats AS DDIUS
JOIN sys.partitions AS P
ON DDIUS.index_id = P.index_id AND
DDIUS.object_id = P.object_id
WHERE
DDIUS.index_id <= 1 /* Clustered Index or Heap */
AND DDIUS.last_user_update IS NOT NULL
GROUP BY
OBJECT_NAME(P.object_id),
DDIUS.last_user_update,
DDIUS.last_system_update
ORDER BY
DDIUS.last_user_update DESC
OPTION
(RECOMPILE);
Biggest issue with this is that if a table hasn't been used since server restart then there won't be data in sys.dm_db_index_usage_stats, so you might miss some that had data changed right before a server restart.
I wouldn't count on this query being perfect, but it should give a good idea of what has been mostly recently updated and how many rows in the table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2014 at 1:31 am
You can follow the below links to capture the row counts for all tables in a database.
http://msdn.microsoft.com/en-us/library/bb510625.aspx
http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply