1USE myDatabase;
2GO
3
4SELECT
5  t.Name                                       AS TableName,
6  s.Name                                       AS SchemaName,
7  p.Rows                                       AS RowCounts,
8  SUM(a.total_pages) * 8                       AS TotalSpaceKB,
9  SUM(a.used_pages) * 8                        AS UsedSpaceKB,
10  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
11FROM
12  sys.tables t
13  INNER JOIN sys.indexes i ON t.object_id = i.object_id
14  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
15  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
16  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
17WHERE
18  t.Name NOT LIKE 'dt%'
19  AND t.is_ms_shipped = 0
20  AND i.object_id > 255
21GROUP BY
22  t.Name, s.Name, p.Rows
23ORDER BY
24  t.Name;
25  
26GO