size of tables in database

quick script to go through the tables of a database and count the number of rows in each.

use databaseName
SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.
EXEC sp_spaceused -- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] INT,
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
) INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT *
FROM #t order by rows desc -- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t

DROP TABLE #t
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.