Query Row Count, Size, and Index Size for Each Table in a Database

I have used this script for years to generate a listing of all the tables and their row counts, size, and index size.  I can’t find where I found it, so if you recognize it, please let me know.  I might get around to rewriting it one day.

 


SET NOCOUNT ON

DECLARE MyTables CURSOR
FOR
SELECT NAME, id
FROM dbo.sysobjects
WHERE xtype = 'U'

DECLARE @name NVARCHAR(517), @dbname NVARCHAR(128)
DECLARE @id INT, @rows INT, @datasizeused INT, @indexsizeused INT, @pagesize INT

SELECT @dbname = db_name()

/* Pagesize on this server (sysindexes stores size info in pages) */
SELECT @pagesize = v.low / 1024
FROM master..spt_values v
WHERE v.number = 1
AND v.type = N'E'

CREATE TABLE #tablesize ( NAME NVARCHAR(517), rows INT, datasizeused INT, indexsizeused INT )

OPEN MyTables

FETCH NEXT
FROM MyTables
INTO @name, @id

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
SELECT @id = id
FROM dbo.sysobjects
WHERE id = object_id(@name)
AND (OBJECTPROPERTY(id, N'IsTable') = 1)

/* rows */
SELECT @rows = convert(INT, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2
AND id = @id

/* data */
SELECT @datasizeused = (
SELECT sum(dpages)
FROM dbo.sysindexes
WHERE indid < 2
AND id = @id
) + (
SELECT isnull(sum(used), 0)
FROM dbo.sysindexes
WHERE indid = 255
AND id = @id
)

/* Do not consider 2 < indid < 255 rows, those are nonclustered indices, and the space used by them are included by indid = 0(table) */
/* or indid = 1(clustered index) already.  indid = 0(table) and = 1(clustered index) are mutual exclusive */
/* index */
SELECT @indexsizeused = (
SELECT sum(used)
FROM dbo.sysindexes
WHERE indid IN (0, 1, 255)
AND id = @id
) - @datasizeused

SELECT @datasizeused = @datasizeused * @pagesize

SELECT @indexsizeused = @indexsizeused * @pagesize

/* Load into temp table */
INSERT INTO #tablesize ( NAME, rows, datasizeused, indexsizeused )
VALUES ( @name, @rows, @datasizeused, @indexsizeused )

FETCH NEXT
FROM MyTables
INTO @name, @id
END

DEALLOCATE MyTables

SET NOCOUNT OFF
GO

SELECT NAME, rows, datasizeused, indexsizeused
FROM #tablesize
ORDER BY datasizeused DESC, rows DESC
GO

DROP TABLE #tablesize
GO

 

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow SQL Uber Geek on WordPress.com
Try Audible and Get Two Free Audiobooks
%d bloggers like this: