RE: how to find name of a table which has maximum number of transactions for a large database

SSC Guru

Points: 323504

December 18, 2012 at 10:48 am

anthony.green (12/18/2012)
I was always told to not trust the sys view as it can differ from what was in the table, that was back in SQL 2000 days so unsure if they are now more or less in sync with each other.

the old rowcounts in sysindexes, yes, they were unreliable. you'd have to use DBCC UPDATEUSAGE before querying sysindexes to get accurate counts in SQL 2000, as i remember.

starting with 2005 and above however, the example i posted is always correct; since there is always an index for every row , whether a heap or a clustered index, you can get the counts accurately.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!