24 February 2009

Find the row counts for all the tables in a SQL Server 2005 database

Have you ever needed to investigate an application database and quickly determine where the records are?
The following script returns a rowset that contains all the table names of a SQL Server 2005 database along with the row counts.

-- declare the variables
DECLARE @i INT, -- integer holder
  @table_name VARCHAR(50), -- table name
  @sql NVARCHAR(800) -- dynamic sql

-- create the temp table. Initialize count column to -1
CREATE TABLE #t (
[name] NVARCHAR(128),
rows CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

SELECT @table_name = [name]
  FROM sysobjects
  WHERE xtype = 'U'

-- Initialize i to run at least once
SET @i = 1

-- loop while rows are still being selected
WHILE (@i > 0)
BEGIN
 -- create the dynamic sql that updates the row counts
 -- for each table
SET @sql = 'INSERT #t ([name], rows, reserved, data, index_size, unused)
EXEC sp_spaceused ['+ @table_name + ']'

 -- execute the dynamic sql
 EXEC sp_executesql @sql

 -- find out the name of the next table 
 SELECT @table_name = [name]
  FROM sysobjects
  WHERE xtype = 'U'
  AND [name] NOT IN
     (
        SELECT [name]
FROM #t
     ) 

 -- stop looking if no rows are selected
 SET @i = @@ROWCOUNT

END

-- return the results
SELECT *
FROM #t
ORDER BY reserved DESC

DROP TABLE #t