Para encontrar quais as maiores tabelas de um banco de dados basta executar o SQL abaixo:
SELECT t.NAME AS TableName,
i.NAME AS indexName,
Sum(p.rows) AS RowCounts,
Sum(a.total_pages) AS TotalPages,
Sum(a.used_pages) AS UsedPages,
Sum(a.data_pages) AS DataPages,
( Sum(a.total_pages) * 8 ) / 1024 AS TotalSpaceMB,
( Sum(a.used_pages) * 8 ) / 1024 AS UsedSpaceMB,
( Sum(a.data_pages) * 8 ) / 1024 AS DataSpaceMB
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
WHERE t.NAME NOT LIKE 'dt%'
AND i.object_id > 255
AND i.index_id <= 1
GROUP BY t.NAME,
i.object_id,
i.index_id,
i.NAME
ORDER BY Object_name(i.object_id)
i.NAME AS indexName,
Sum(p.rows) AS RowCounts,
Sum(a.total_pages) AS TotalPages,
Sum(a.used_pages) AS UsedPages,
Sum(a.data_pages) AS DataPages,
( Sum(a.total_pages) * 8 ) / 1024 AS TotalSpaceMB,
( Sum(a.used_pages) * 8 ) / 1024 AS UsedSpaceMB,
( Sum(a.data_pages) * 8 ) / 1024 AS DataSpaceMB
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
WHERE t.NAME NOT LIKE 'dt%'
AND i.object_id > 255
AND i.index_id <= 1
GROUP BY t.NAME,
i.object_id,
i.index_id,
i.NAME
ORDER BY Object_name(i.object_id)