Ver información de todas las tablas de una base de datos.

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.