Since I am in the Collation Sensitive mood, I am finally getting around to updating this script. This is the Table Space script that I have had out there for a while. In the last release of this script, a request (by Remi) was made to update it so it will work with CS. In addition to that, a request was made to add a few columns. I have done both.
The CS request was not too big of a deal – just took a minute to actually sit down and do it. Then it was a matter of setting a test database to CS and confirming that the script continued to work. A friend did the same legwork (thx Remi) and posted his update in a thread I had been planning on getting back to with the update. Now it will just get a link to this, and then there can be a circular reference.
The second part of the request was for a change in calculations and possibly additional columns. I just added columns and someday hope to get back to this script and parameterize the whole thing so that a variable set of columns can be returned – based on user input. Oh the glory of those someday goals.
So, here is the updated Table Size script.
DECLARE @dbsize DECIMAL(19,2) ,@logsize DECIMAL(19,2) SET NOCOUNT ON /* ** Summary data. */ BEGIN SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024 , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024 FROM sys.database_files END ;WITH FirstPass AS ( SELECT OBJECT_ID, ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024, UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024, PageCnt = SUM( CONVERT(DECIMAL(19,2),CASE WHEN (index_id < 2) THEN (used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END )) * 8/1024, RowCnt = SUM( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) FROM sys.dm_db_partition_stats --Where OBJECTPROPERTY(object_id,'IsMSShipped') = 0 GROUP BY OBJECT_ID ) ,InternalTables AS ( SELECT ps.OBJECT_ID, ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024, UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024 FROM sys.dm_db_partition_stats ps INNER Join sys.internal_tables it ON it.OBJECT_ID = ps.OBJECT_ID And it.internal_type IN (202,204,211,212,213,214,215,216) WHERE it.parent_id = ps.OBJECT_ID --And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0 GROUP BY ps.OBJECT_ID ) ,Summary AS ( SELECT ObjName = OBJECT_NAME (F.OBJECT_ID), NumRows = MAX(F.RowCnt), ReservedPageMB = SUM(IsNull(F.ReservedPage,0) + IsNull(i.ReservedPage,0)), DataSizeMB = SUM(F.PageCnt), IndexSizeMB = SUM(CASE WHEN (F.UsedPage + IsNull(i.UsedPage,0)) > F.PageCnt THEN ((F.UsedPage + IsNull(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats UnusedSpace = SUM(CASE WHEN (F.ReservedPage + IsNull(i.ReservedPage,0)) > (F.UsedPage + IsNull(i.UsedPage,0)) THEN ((F.ReservedPage + IsNull(i.ReservedPage,0)) - (F.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END), dbsizeMB = @dbsize, LogSizeMB = @logsize FROM FirstPass F LEFT Outer Join InternalTables i ON i.OBJECT_ID = F.OBJECT_ID GROUP BY F.OBJECT_ID ),TotalUnused AS ( SELECT SUM(UnusedSpace) AS UnusedSpace FROM Summary ) SELECT ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, S.UnusedSpace, dbsizeMB, LogSizeMB ,dbsizeMB - TU.UnusedSpace AS TotalDataFreeSpace ,PercentofDBPhysFile = ((IndexSizeMB + DataSizeMB) / @dbsize) * 100 ,PercentofDBUsedSpace = ((IndexSizeMB + DataSizeMB) / (@dbsize - TU.UnusedSpace)) * 100 FROM Summary S CROSS APPLY TotalUnused TU ORDER BY PercentofDBUsedSpace DESC
If you recall, I did two versions of the table size script. One followed the path of sp_spaceused and the other followed sp_MStablespace. This script is the one that follows the sp_spaceused version. I will post an update for the sp_MStablespace version shortly.