Informacion Registros y Tamaño de Tablas en SQL Server

Buenos dias a tod@os,

Hoy os traigo otro pequeño script para SQL Server, con el que podreis sacar bastante informacion de tablas, tales como tamaños, numero de registros, etc…

SELECT
   t.NAME AS NombreTabla,
   s.Name AS Esquema,
   p.rows AS NumFilas,
   SUM(a.total_pages) * 8 AS EspacioTotal_KB,
   SUM(a.used_pages) * 8 AS EspacioUsado_KB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacioNoUsado_KB
FROM
   sys.tables t
     INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
     INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
   t.is_ms_shipped = 0
     AND i.OBJECT_ID > 255
GROUP BY
   t.Name, s.Name, p.Rows
ORDER BY
   p.rows desc, t.NAME, s.Name desc

SELECT

t.NAME AS NombreTabla,

s.Name AS Esquema,

p.rows AS NumFilas,

SUM(a.total_pages) * 8 AS EspacioTotal_KB,

SUM(a.used_pages) * 8 AS EspacioUsado_KB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacioNoUsado_KB

FROM

sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

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

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

p.rows desc, t.NAME, s.Name desc

El resultado de esta consulta seria algo como esto:

Como veis se puede obtener dicha informacion de forma rapida, os recomiendo que mires las tablas de las que se esta estrayendo la informacion, ya que podeis obtener mucha mas.