Advertisement
brunobola

Untitled

May 8th, 2023
109
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.08 KB | None | 0 0
  1. SELECT
  2.     t.NAME AS TableName,
  3.     s.Name AS SchemaName,
  4.     p.ROWS AS RowCounts,
  5.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
  6.     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
  7.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
  8.     CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
  9.     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
  10.     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
  11. FROM
  12.     sys.TABLES t
  13. INNER JOIN      
  14.     sys.indexes i ON t.OBJECT_ID = i.object_id
  15. INNER JOIN
  16.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  17. INNER JOIN
  18.     sys.allocation_units a ON p.partition_id = a.container_id
  19. LEFT OUTER JOIN
  20.     sys.schemas s ON t.schema_id = s.schema_id
  21. WHERE
  22.     t.NAME NOT LIKE 'dt%'
  23.     AND t.is_ms_shipped = 0
  24.     AND i.OBJECT_ID > 255
  25. GROUP BY
  26.     t.Name, s.Name, p.ROWS
  27. ORDER BY
  28.     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) DESC
  29.     --t.Name
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement