About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, January 27, 2014

Query storage size on your tables or tables in the database.

A helpful query on finding the size of your specific tables in SQL Server. Just replace with your string list of tables.

Code Snippet
  1. SELECT
  2. sum(RowCounts) as RowCounts,
  3. SUM(TotalSpaceKB) AS TotalSpaceKB,
  4. SUM(UsedSpaceKB) AS UsedSpaceKB,
  5. SUM(UnusedSpaceKB) AS UnusedSpaceKB
  6. FROM
  7. (
  8.     SELECT
  9.         sum(p.rows) AS RowCounts,
  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
  13.     FROM
  14.         sys.tables t
  15.     INNER JOIN      
  16.         sys.indexes i ON t.OBJECT_ID = i.object_id
  17.     INNER JOIN
  18.         sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  19.     INNER JOIN
  20.         sys.allocation_units a ON p.partition_id = a.container_id
  21.     WHERE
  22.         t.is_ms_shipped = 0
  23.         AND i.OBJECT_ID > 255
  24.         AND t.NAME IN ( <TABLE_NAMES> )
  25.     GROUP BY
  26.        p.Rows
  27. )x

Or to get a list of all tables and their sizes in the database:

Code Snippet
  1. SELECT
  2.     t.NAME as TableName,
  3.     sum(p.rows) AS RowCounts,
  4.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
  5.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
  6.     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
  7. FROM
  8.     sys.tables t
  9. INNER JOIN      
  10.     sys.indexes i ON t.OBJECT_ID = i.object_id
  11. INNER JOIN
  12.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  13. INNER JOIN
  14.     sys.allocation_units a ON p.partition_id = a.container_id
  15. WHERE
  16.     t.is_ms_shipped = 0
  17.     AND i.OBJECT_ID > 255
  18. GROUP BY
  19.    t.Name, p.Rows

No comments: