SQL Function - GetAllTablesByteSize( )

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        tcarter
-- Create date: 7/9/2014
-- Description:    ALL TABLES SIZES
-- =============================================
CREATE FUNCTION GetAllTablesByteSize
(    
    @DBNAME VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
 
        SELECT TOP 1000
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB,  
        SUM(a.used_pages) * 8 AS UsedSpaceKB,  
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
        FROM  
            sys.tables t
        INNER JOIN       
            sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN  
            sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN  
            sys.allocation_units a ON p.partition_id = a.container_id
        LEFT OUTER JOIN  
            sys.schemas s ON t.schema_id = s.schema_id
        WHERE  
            t.NAME NOT LIKE 'dt%'  
            AND t.is_ms_shipped = 0
            AND i.OBJECT_ID > 255  
        GROUP BY  
            t.Name, s.Name, p.Rows
        ORDER BY  
            UsedSpaceKB desc, t.Name
)
GO