Identifying unused space consuming tables in SQL Server

 

You might have an SQL Server database with lot of unnecessary tables. But you are unable to remove them because you don't know if it is still being used or not. Sometimes they have been created as a backup and not deleted so now it's there as a redundant and taking up your space. In order to identify such tables the below query comes in handy. You can check the Last Interaction date and the table size to determine what to do. As a best practice it is better to ha

ve a last updated date as a column in tables to identify the current relevant tables.

SELECT
  last_user_scan LastInteraction,
  t.NAME AS TableName,
  s.Name AS SchemaName,
  p.rows AS RowsCount,
  SUM(a.total_pages) * 8 AS TotalSpaceKB,
  CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS TotalSpaceMB,
  SUM(a.used_pages) * 8 AS UsedSpaceKB,
  CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS UsedSpaceMB,
  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
  CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS numeric(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats j
  ON j.object_id = t.object_id
GROUP BY t.Name,
         s.Name,
         p.Rows,
         [modify_date],
         last_user_scan
ORDER BY TotalSpaceMB DESC, last_user_scan, t.Name