/****** Object:  StoredProcedure [dbo].[xspGetTableSpaceUsed]    Script Date: 16/11/2012 09:35:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[xspGetTableSpaceUsed] 
AS 
BEGIN 
    SET NOCOUNT ON      
 
    DBCC UPDATEUSAGE(0) 
 
    CREATE TABLE #t
    ( 
        id INT, 
        TableName VARCHAR(100), 
        NRows INT, 
        Reserved FLOAT, 
        TableSize FLOAT, 
        IndexSize FLOAT, 
        FreeSpace FLOAT 
    ) 
 
    INSERT #t EXEC  sp_msForEachTable 'SELECT 
        OBJECT_ID(PARSENAME(''?'',1)), 
        PARSENAME(''?'',1), 
        COUNT(*),0,0,0,0 FROM ?' 
 
    DECLARE @low INT 
 
    SELECT @low = [low] FROM master.dbo.spt_values 
        WHERE number = 1 
        AND type = 'E' 
 
    UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM 
        (SELECT id, r = SUM(si.reserved), i = SUM(si.used) 
        FROM sysindexes si 
        WHERE si.indid IN (0, 1, 255) 
        GROUP BY id) x 
        WHERE x.id = #t.id 
 
    UPDATE #t SET TableSize = (SELECT SUM(si.dpages) 
        FROM sysindexes si 
        WHERE si.indid < 2 
        AND si.id = #t.id) 
 
    UPDATE #t SET TableSize = TableSize + 
        (SELECT COALESCE(SUM(used), 0) 
        FROM sysindexes si 
        WHERE si.indid = 255 
        AND si.id = #t.id) 
 
    UPDATE #t SET FreeSpace = Reserved - IndexSize 
 
    UPDATE #t SET IndexSize = IndexSize - TableSize 
 
    SELECT 
        TableName, 
        nrows as NumRows, 
        Reserved2 = LTRIM(STR( 
            reserved * @low / 1024.,15,0) + 
            ' ' + 'KB'), 
        DataSize = LTRIM(STR( 
            tablesize * @low / 1024.,15,0) + 
            ' ' + 'KB'), 
        IndexSize = LTRIM(STR( 
            indexSize * @low / 1024.,15,0) + 
            ' ' + 'KB'), 
        FreeSpace = LTRIM(STR( 
            freeSpace * @low / 1024.,15,0) + 
            ' ' + 'KB') 
        FROM #t 
        ORDER BY reserved desc
		--ORDER BY TableName
 
    DROP TABLE #t 
END

go