SQL Server Index Size

IndexSize.sql

if object_id('dbo.IndexSize', 'FN') is not null
begin
	drop function dbo.IndexSize	
end
go
create function [dbo].[IndexSize](@object_id int)
returns float
as
begin
	declare @PageSize float
	select  @PageSize = v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type=N'E'

	declare @IndexSize float
	select  @IndexSize = 
        case
        when (tbl.is_memory_optimized=0) then
            isnull((
            (select sum (used_page_count) from sys.dm_db_partition_stats ps where ps.object_id = tbl.object_id)
            + ( case (select count(*) from sys.internal_tables where parent_id = tbl.object_id and internal_type IN (202,204,207,211,212,213,214,215,216,221,222))
                when 0 then 0
                else (
                  select sum(p.used_page_count)
                  from sys.dm_db_partition_stats p, sys.internal_tables it
                  where it.parent_id = tbl.object_id and it.internal_type in (202,204,207,211,212,213,214,215,216,221,222) and p.object_id = it.object_id)
                end )
            - (select sum (case when(index_id < 2) then (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) else 0 end)
               from sys.dm_db_partition_stats where object_id = tbl.object_id)
            ) * @pagesize, 0.0)
        else
            isnull((select (tms.[memory_used_by_indexes_kb])
            from [sys].[dm_db_xtp_table_memory_stats] tms
            where tms.object_id = tbl.object_id), 0.0)
        end
	from    sys.tables as tbl
	where   object_id = @object_id

	return	@IndexSize
end
go