Get tables size from SQL Server database

use SqlMonitor
go

DECLARE @FName AS NVARCHAR(500)
DECLARE @FNameFull AS NVARCHAR(500)
DECLARE @Schema AS NVARCHAR(500)
DECLARE @SchemaName AS NVARCHAR(500)

CREATE TABLE #temp
    (
      [name] NVARCHAR(3000) ,
      [rows] INT ,
      reserved NVARCHAR(3000) ,
      [data] NVARCHAR(3000) ,
      [index_size] NVARCHAR(3000) ,
      unused NVARCHAR(3000)
    )


DECLARE cur CURSOR FAST_FORWARD
FOR
    SELECT  name ,
            schema_id
    FROM    sys.objects
    WHERE   is_ms_shipped = 0
            AND type = 'U'


OPEN cur

FETCH NEXT FROM cur INTO @FName, @Schema

WHILE @@FETCH_STATUS = 0
    BEGIN


        SELECT  @SchemaName = NAME
        FROM    sys.schemas
        WHERE   schema_id = @Schema

        IF ( @SchemaName = 'data' )
            BEGIN

                SET @FNameFull = @SchemaName + '.' + @FName
                INSERT  INTO #temp
                        EXEC sp_spaceused @FNameFull
            END
        FETCH NEXT FROM cur INTO @FName, @Schema

    END

CLOSE cur
DEALLOCATE cur
SELECT  *
FROM    #temp
ORDER BY rows DESC
DROP TABLE #temp