DECLARE @SESSION_ID AS BIGINT;
                  SET @SESSION_ID = (SELECT MAX(session_id)+1 AS SESSION_ID FROM DLA.schema_status) 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dla].[schema_status]
AS
BEGIN

DECLARE @SESSION_ID AS BIGINT;
SET @SESSION_ID = (SELECT MAX(session_id)+1 AS SESSION_ID FROM DLA.schema_status);



INSERT INTO dla.schemastatus WITH (TABLOCK)
SELECT 
      @SESSION_ID SESSION_ID,
	  Schemaname Schemaname,
	  SizeInMB SIzeInMB,
	  GETDATE() date
	FROM(
	     SELECT  SCHEMA_NAME(so.schema_id) AS SchemaName
               ,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMB
        FROM    sys.dm_db_partition_stats ps
        JOIN    sys.indexes i
          ON    i.object_id                                     =           ps.object_id
         AND    i.index_id                                      =           ps.index_id
             JOIN   sys.objects  so
               ON   i.object_id                                                              =                   so.object_id
       WHERE    so.type                                                                       =                   'U'
    GROUP BY so.schema_id
    ---ORDER BY OBJECT_SCHEMA_NAME(so.schema_id), SizeInMB