CREATE PROCEDURE SP_GetTableSpace

AS

SET NOCOUNT ON

CREATE TABLE #TableSpace (
 Rows int,
 DataSpaceUsed int,
 IndexSpaceUsed int
 )

DECLARE @TableSpace table (
 TableName varchar(255),
 Rows int,
 DataSpaceUsed int,
 IndexSpaceUsed int
 )

DECLARE
 @Rows int,
 @DataSpaceUsed int,
 @IndexSpaceUsed int,
 @TableName varchar(255)

DECLARE Table_Cursor CURSOR FOR
 SELECT user_name(o.uid) + '.' + o.name AS table_name
 FROM dbo.sysobjects o, dbo.sysindexes i
 WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
 AND i.id = o.id
 AND i.indid < 2
 AND o.name NOT LIKE N'#%'
 AND xtype = 'U'
 ORDER BY 1

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
 BEGIN

INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
 EXEC sp_MStablespace @TableName

SELECT @Rows = Rows,
 @DataSpaceUsed = DataSpaceUsed,
 @IndexSpaceUsed = IndexSpaceUsed
 FROM #TableSpace

INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
 VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)

DELETE FROM #TableSpace

FETCH NEXT FROM Table_Cursor INTO @TableName
 END

CLOSE Table_Cursor
 DEALLOCATE Table_Cursor

DROP TABLE #TableSpace

SELECT *
 FROM @TableSpace
 ORDER BY DataSpaceUsed+IndexSpaceUsed DESC