MS SQL: Tamaño ocupado por las tablas de una BBDD

Muchas gracias a http://www.textil.org/sistemas/index.php/2009/02/tamanyo-tablas-y-vistas-indexadas-en-sql-server/ por colgar en su web un script para ver el tamaño que ocupa en disco las tablas de una base de datos:

— Cursor que contiene todos los objetos que ocupan espacio

DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT name

FROM sysobjects o

WHERE

o.xtype = ‘U’

CREATE TABLE #results (

name SYSNAME, rows CHAR(11),

reserved VARCHAR(18), data VARCHAR(18),

index_size VARCHAR(18),Unused VARCHAR(18))

–Recorremos el cursor obteniendo la información de espacio ocupado

DECLARE @object_name AS SYSNAME

OPEN objects_cursor

FETCH NEXT FROM objects_cursor

INTO @object_name;

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #results

EXEC sp_spaceused @object_name

FETCH NEXT FROM objects_cursor

INTO @object_name;

END;

CLOSE objects_cursor;

DEALLOCATE objects_cursor;

UPDATE #results
SET
reserved = LEFT(reserved,LEN(reserved)-3),
data = LEFT(data,LEN(data)-3),
index_size = LEFT(index_size,LEN(index_size)-3),
Unused = LEFT(Unused,LEN(Unused)-3)

Advertisements
MS SQL: Tamaño ocupado por las tablas de una BBDD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s