Posted on

Get All Table Sizes

use master
go

CREATE PROCEDURE sp_GetAllTableSizes
AS
BEGIN
DECLARE @tabname varchar(128)

DECLARE table_cur CURSOR FAST_FORWARD FOR
SELECT [name]
FROM dbo.sysobjects (NOLOCK)
WHERE OBJECTPROPERTY(id, N’IsUserTable’) = 1

CREATE TABLE #TempTable
(
tableName varchar(128),
numberofRows int,
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

OPEN table_cur

FETCH NEXT FROM table_cur INTO @tabname

WHILE (@@Fetch_Status >= 0)
BEGIN
INSERT #TempTable
EXEC sp_spaceused @tabname

FETCH NEXT FROM table_cur INTO @tabname
END

CLOSE table_cur
DEALLOCATE table_cur

SELECT *
FROM #TempTable
ORDER BY numberofRows DESC

DROP TABLE #TempTable
END –sp_GetAllTableSizes
GO