He creado el siguiente script que nos permite listar todas las tablas en una base de datos SQL Server con los detalles del espacio utilizado y los tipos de índices en cada una de ellas; esta información es útil para manejar nuestras bases de datos de SQL Server.
SELECT systablesname AS [TABLE], systablescreate_date AS CREATE_DATE, systablesmodify_date AS MODIFY_DATE,
CASE WHEN sysdatabase_principalsname IS NULL THEN SCHEMA_NAME(systablesschema_id)
ELSE sysdatabase_principalsname END AS OWNER,
SUM(ISNULL(CASE INDEXESTYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,
SUM(ISNULL(CASE INDEXESTYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXESTYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXESTYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,
SUM(ISNULL(CASE INDEXESTYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,
systablesmax_column_id_used AS COUNT_COLUMNS, syspartitionsrows AS COUNT_ROWS,
SUM(ISNULL(CASE WHENsysallocation_unitstype <> 1 THEN USED_PAGES
WHEN SYSpartitionsINDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *
(SELECT low / 1024 AS VALUE FROM masterdbospt_values
WHERE (number = 1) AND(type = N'E')) AS SIZE_DATA_KB,
SUM(ISNULL(sysallocation_unitsused_pages - CASE WHEN sysallocation_unitstype <> 1 THEN USED_PAGES
WHEN SYSpartitionsINDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *(SELECT low / 1024 AS VALUE
FROM masterdbospt_values AS spt_values_2 WHERE (number = 1) AND(type = N'E')) AS SIZE_INDEX_KB
FROM sysallocation_units INNERJOIN syspartitions ON sysallocation_unitscontainer_id = syspartitionspartition_id
INNER JOIN(SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, COUNT(*) AS COUNT_TYPE
FROM sysindexes AS indexes_1 GROUP BY object_id, type, index_id ORDER BY object_id) AS INDEXES ON
syspartitionsobject_id = INDEXESobject_id AND syspartitionsindex_id = INDEXESindex_id RIGHT OUTER JOIN
sysdatabase_principals RIGHT OUTER JOIN systables ON sysdatabase_principalsprincipal_id = systablesprincipal_id ON
INDEXESobject_id = systablesobject_id GROUP BY systablesname, systablescreate_date, systablesmodify_date,
CASE WHEN sysdatabase_principalsname IS NULL THEN SCHEMA_NAME(systablesschema_id) ELSE sysdatabase_principalsname END,
systablesmax_column_id_used, syspartitionsrows
ORDER BYCOUNT_ROWS DESC
Como siempre espero que lo prueben y les sea de mucha utilidad.
Clic aquí para descargar el script.