--
-- (c)Copyrights 2009 Nederpelt Software
-- Release 2, sql 2005 schat het aantal rows gebruikt, aangepast.
-- Written by Alwin Nederpelt
--
-- Roept de grootte van de database op aan de hand van interne SQL routines
--
IF OBJECT_ID('GetTableSpace') IS NOT NULL DROP PROC GetTableSpace
GO

CREATE PROCEDURE 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
DECLARE @TableName varchar(255)
DECLARE @hitcount int
DECLARE @sql nvarchar(4000)
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
---------------------------------
--Set Data
FETCH NEXT FROM Table_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0


    BEGIN
    	---------------------------------
    	--Set Data
    	
    	
    	INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
    	EXEC sp_MStablespace @TableName

		SELECT @sql = 'SELECT @hitcount=COUNT(*) FROM ' + @TableName
		EXEC sp_executesql @sql, N'@hitcount int OUTPUT', @hitcount OUTPUT

		SELECT	@Rows = @hitcount, 
    		@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 SUM(DataSpaceUsed+IndexSpaceUsed) AS TotalSpace_Kb,SUM(DataSpaceUsed+IndexSpaceUsed)/1024 AS TotalSpace_Mb,SUM(DataSpaceUsed+IndexSpaceUsed)/1024/1024 AS TotalSpace_Gb
FROM @TableSpace
SELECT *,DataSpaceUsed+IndexSpaceUsed AS TotalSpace_Kb,(DataSpaceUsed+IndexSpaceUsed)/1024 AS TotalSpace_Mb,ROUND((DataSpaceUsed+IndexSpaceUsed)/1024/1024,2) AS TotalSpace_Gb
FROM @TableSpace
ORDER BY dataspaceused+indexspaceused DESC
GO

exec GetTableSpace
