I was reviewing some of my older scripts and I came across one in particular that stood out. I wrote it quite a while ago and looking back does indicate where I came from in terms of logic. To give a little background my script was written to grab the row counts from all tables within the given database. The problem… it created a temp table then loaded the temp table from querying the INFORMATION_SCHEMA.TABLES. Then it looped through each of the table names loading them it into a variable and then passed them to an update statement which updated the temp table. As you can see this was a very inefficient way to go about it.
I was reading up on catalog views and it seemed that I could query sys.sysobjects and join sys.sysindexes. So I figured I would get this verified. I posed the question and following are the responses I received.
Yep. sys.sysindexes is depreciated in Denali. Information here: msdn.microsoft.com
So here is my script. I originally left out line 15 and when I did I saw a lot of duplication in the results set. So I surfed around and found a post titled, “Display Row count for all tables” by Shyam Skj that used the indid field from sysindexes to filter the records. The closes thing I found was the index_id from sys.partitions and lo and behold using similar syntax the results returned nicely.
/* Check if object already exists */ IF OBJECT_ID('usp_GetTableRowCounts') IS NOT NULL /* If available drop object */ BEGIN DROP PROCEDURE usp_GetTableRowCounts END GO /* Create procedure */ CREATE PROCEDURE usp_GetTableRowCounts AS SELECT o.name 'TableName', p.rows 'Rows' FROM sys.objects o JOIN sys.partitions p ON o.object_id = p.object_id WHERE (o.type = 'U') AND (p.index_id IN (0,1)) ORDER BY p.rows DESC