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
