Espacio en tablas e índices en Sql Server

Hola, hoy dejo un par de scripts para ver el espacio en disco consumido tanto por tablas como por índices, en Sql Server.

-- Espacio por tabla

DECLARE ctables CURSOR

FOR

SELECT [name]

FROM dbo.sysobjects

WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

 

CREATE TABLE #tables

    (

      tableName VARCHAR(100) ,

      numberofRows VARCHAR(100) ,

      reservedSize VARCHAR(50) ,

      dataSize VARCHAR(50) ,

      indexSize VARCHAR(50) ,

      unusedSize VARCHAR(50)

    )

 

OPEN ctables

 

DECLARE @tableName VARCHAR(100)

 

FETCH NEXT FROM ctables INTO @tableName

 

WHILE ( @@Fetch_Status >= 0 )

    BEGIN

        INSERT  #tables

                EXEC sp_spaceused @tableName

        FETCH NEXT FROM ctables INTO @tableName

    END

 

CLOSE ctables

DEALLOCATE ctables

 

UPDATE #tables SET reservedSize = SUBSTRING(reservedSize, 0, CHARINDEX(' ', reservedSize))

UPDATE #tables SET dataSize = SUBSTRING(dataSize, 0, CHARINDEX(' ', dataSize))

UPDATE #tables SET indexSize = SUBSTRING(indexSize, 0, CHARINDEX(' ', indexSize))

UPDATE #tables SET unusedSize = SUBSTRING(unusedSize, 0, CHARINDEX(' ', unusedSize))

 

CREATE TABLE #tables2

    (

      tableName VARCHAR(100) ,

      numberofRows VARCHAR(100) ,

      reservedSize INT ,

      dataSize INT ,

      indexSize INT  ,

      unusedSize INT

    )

   

INSERT INTO #tables2

SELECT * FROM #tables       

 

DROP TABLE #tables

 

SELECT  *

FROM    #tables2

ORDER BY reservedSize DESC

 

DROP TABLE #tables2

 

-- Espacio por índice

SELECT * FROM (

SELECT  i.[object_id] ,

        OBJECT_NAME(i.object_id) AS tableName ,

        i.name ,

        i.type_desc ,

        p.partition_number ,

        p.rows ,

        a.total_pages * 8 AS [reserved (KB)] ,

        (a.total_pages * 8) / 1024 AS [reserved (MB)] ,

        a.used_pages * 8 AS [used (KB)]

FROM    sys.indexes AS i

        INNER JOIN sys.partitions AS p ON i.object_id = p.object_id

                                          AND i.index_id = p.index_id

        INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id

WHERE   i.index_id > 1 -- índice no agrupado

             --AND i.[object_id] = object_id('dbo.Sales')

) AS T

WHERE tableName NOT LIKE ('sys%') AND tableName NOT LIKE ('queue[_]%')

AND tableName NOT LIKE ('aspnet%')

ORDER BY [reserved (kb)] DESC

Un saludo!.