sql server table size

1
2/******* Calcula tamany de totes les taules  *********/
3SELECT 
4    s.Name AS SchemaName,
5    t.NAME AS TableName,
6    p.rows AS RowCounts,
7    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
8    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
9    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB,
10    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
11    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
12    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
13FROM 
14    sys.tables t
15INNER JOIN      
16    sys.indexes i ON t.OBJECT_ID = i.object_id
17INNER JOIN 
18    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
19INNER JOIN 
20    sys.allocation_units a ON p.partition_id = a.container_id
21LEFT OUTER JOIN 
22    sys.schemas s ON t.schema_id = s.schema_id
23WHERE 
24    t.NAME NOT LIKE 'dt%' 
25    AND t.is_ms_shipped = 0
26    AND i.OBJECT_ID > 255 
27GROUP BY 
28    t.Name, s.Name, p.Rows
29ORDER BY 
30      TotalSpaceMB desc
31;
32
33
34/************ Calcula tamany dels indexos ****************/
35SELECT
36      OBJECT_NAME(i.OBJECT_ID)     AS NomTaula
37    , i.name                       AS NomIndex
38    , SUM(a.used_pages) * 8 / 1024 AS TamanyMB
39FROM
40	  sys.indexes          AS i
41	, sys.partitions       AS p
42	, sys.allocation_units AS a
43WHERE
44	  i.OBJECT_ID     = p.OBJECT_ID
45  AND i.index_id      = p.index_id
46  AND p.partition_id  = a.container_id
47  AND i.is_primary_key = 0 -- excloure PK's
48GROUP BY
49      i.OBJECT_ID
50    , i.index_id
51    , i.name
52ORDER BY
53	TamanyMB DESC,
54    NomTaula,
55    i.index_id
56;
57