Table Space updated again

Today we have another update for an age old script on this site.  You can find the last update to the script here.

This time we have a bit of a bug update.  The details of that bug are in the notes for the script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

/*
Purpose:
To Evaluate table sizes combined with index space consumption to determine higher cost tables in 
terms of storage, resources and maintenance needs.

ModifiedDateModifiedByDescription
2013-11-21JBTables without Indexes had a Null Value in the Output
Fixed the output to sum properly for the NULLS in absence of an index

*/BEGIN TRAN

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)) / 128.0
                , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN size ELSE 0 END)) / 128.0
                FROM sys.database_files

END;
        WITH FirstPass AS (
                SELECT object_id,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0,
                        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,
                        iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) * 8/1024,
                        RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1
--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
AND index_id < 2
                GROUP BY object_id
        ),IndexPass AS (
                SELECT object_id,
                        iReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,iUsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                        ,iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) / 128.0
                        ,RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                Where 1 = 1 
--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
AND index_id > 1
                GROUP BY object_id
        ),InternalTables AS (
                SELECT ps.object_id,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                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
                        OBJECT_NAME (F.object_id) AS ObjName 
                        ,SCHEMA_NAME(o.schema_id) AS SchemaName
                        ,CASE WHEN OBJECTPROPERTY(F.object_id,'IsMSShipped') = 1 
THEN 'YES' 
ELSE 'NO' 
END AS IsMsShipped
                        ,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
,IndexSizeMB = SUM(ISNULL(ip.iPageCnt,0))
                        ,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)
                                + (SUM(ISNULL(ip.iReservedPage,0)) - SUM(ISNULL(ip.iUsedPage,0)))
                        ,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))
                        ,dbsizeMB = @dbsize
                        ,LogSizeMB = @logsize
                FROM FirstPass F
INNER JOIN sys.objects o
ON F.object_id = o.object_id
LEFT Outer Join InternalTables i
                        ON i.object_id = F.OBJECT_ID
                    LEFT OUTER JOIN IndexPass ip
ON F.OBJECT_ID = ip.OBJECT_ID
                GROUP BY F.object_id,o.schema_id
        ),TotalUnused AS (
SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace
,SUM(ISNULL(reservedpageMB,0))+SUM(ISNULL(IndexReservedMB,0)) as Usedr
FROM Summary
)
        SELECT ObjName,SchemaName,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) as DataSizeMB, ISNULL(IndexSizeMB,0) as IndexSizeMB
,ISNULL(S.UnusedSpace,0) as UnusedSpace, dbsizeMB, LogSizeMB
,TU.UnusedSpace AS TotalTableFreeSpace 
                        ,dbsizeMB - TU.Usedr as DataFileFreeSpace  
                        /*within 1.5gb on a 1.76tb database or .000085% variance or 99.999915% accuracy */                        ,PercentofDBPhysFile = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / @dbsize) * 100
                        ,PercentofDBUsedSpace = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / (@dbsize - TU.UnusedSpace)) * 100

        FROM Summary S
CROSS APPLY TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC

ROLLBACK

Alternatively, you can download the script from here (in case a plugin update breaks the script again).