In continuation with the previous article, this will help to identify which tables in your database are eating up space, maybe it’s just a log table or a temp table, that you may wipe off to save the space and optimize the performance of your DB. Here is how you would do this
SELECT
systable.NAME AS TableName,
sysschema.Name AS SchemaName,
syspartition.rows AS RowCounts,
SUM(sysallocation.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(sysallocation.total_pages) * 8) / (1024 * 1024)), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
CAST(ROUND(((SUM(sysallocation.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(sysallocation.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(sysallocation.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(sysallocation.total_pages) - SUM(sysallocation.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(sysallocation.total_pages) - SUM(sysallocation.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables systable
INNER JOIN
sys.indexes sysindex ON systable.OBJECT_ID = sysindex.object_id
INNER JOIN
sys.partitions syspartition ON sysindex.object_id = syspartition.OBJECT_ID AND sysindex.index_id = syspartition.index_id
INNER JOIN
sys.allocation_units sysallocation ON syspartition.partition_id = sysallocation.container_id
LEFT OUTER JOIN
sys.schemas sysschema ON systable.schema_id = sysschema.schema_id
WHERE
systable.NAME NOT LIKE 'dt%'
AND systable.is_ms_shipped = 0
AND sysindex.OBJECT_ID > 255
GROUP BY
systable.Name, sysschema.Name, syspartition.Rows
ORDER BY
TotalSpaceMB desc
based on this you may decide to truncate or delete the tables to save the space on the database. Hope this helps.