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.
[codesyntax lang=”tsql”]
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[/codesyntax]
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.
