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