CREATE SEQUENCE prm.sq_etl_log_1
AS bigint
START WITH 1
INCREMENT BY 1
CREATE TABLE prm.dwh_size_of_tables(
  ddate date NULL,              --Дата  на момент который смотрим статистику таблицы
  run_id numeric(14, 0) NOT NULL,  --ID Запуска сбора статистики, Счетчик
  db_name varchar(20) NOT NULL,  --База данных
  schema_name sysname NOT NULL,  --Схема таблицы
  table_name sysname NOT NULL,  --Название таблицы
  row_count bigint NULL,        --Количество строк в таблице
  reserved_KB bigint NULL,      --Ощий размер таблицы  вместе с индесами
  data_KB bigint NULL,          --Размер самих данных в таблице
  index_size_KB bigint NULL,    --Размер индексов
  unused_KB bigint NULL          --неиспрользованное место
)

USE [LEMON]
GO
CREATE  PROCEDURE  [prm].[load_etl_log]
AS
declare
@run_id int
BEGIN
--Если сегодня был запуск очищаем текущюую статистику и перезаливаем
delete from lemon.prm.dwh_size_of_tables where ddate = cast(getdate() as date);
--Для страых периодов  храним только статистику только на начало и на середину месяца
delete from  lemon.prm.dwh_size_of_tables
where (DATEPART(day, ddate)not in (1,15) and ddate < dateadd(month ,-2, getdate()))
DECLARE @SQL_text varchar(max),@SQL_text_final varchar(max); ;
set @SQL_text=   '
USE {SCHEMA_FOR_REPLACE};
insert into  lemon.prm.dwh_size_of_tables
SELECT
  cast(getdate() as date) date_time,
  '''+ convert(nvarchar , @run_id  ) +''' run_id ,
  ''{SCHEMA_FOR_REPLACE}'' db_name,
  a3.name AS schema_name
  ,--Схема
  a2.name AS table_name
  ,--Имя таблицы
  a1.rows AS row_count
  ,--Число записей
  (a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS reserved_KB
  ,--Зарезервировано (КБ)
  a1.data * 8 AS data_KB
  ,--Данные (КБ)
  (
    CASE
      WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
        THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
      ELSE 0
      END
    ) * 8 AS index_size_KB
  ,--Индексы (КБ)
  (
    CASE
      WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
        THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
      ELSE 0
      END
    ) * 8 AS unused_KB --Не используется (КБ)
  FROM (
        SELECT ps.object_id
          ,SUM(CASE
              WHEN (ps.index_id < 2)
                THEN row_count
              ELSE 0
              END) AS [rows]
          ,SUM(ps.reserved_page_count) AS reserved
          ,SUM(CASE
              WHEN (ps.index_id < 2)
                THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
              ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
              END) AS data
          ,SUM(ps.used_page_count) AS used
        FROM sys.dm_db_partition_stats ps
        WHERE ps.object_id NOT IN (
            SELECT object_id
            FROM sys.tables
            WHERE is_memory_optimized = 1
            )
        GROUP BY ps.object_id
    ) AS a1
  LEFT OUTER JOIN (
        SELECT it.parent_id
          ,SUM(ps.reserved_page_count) AS reserved
          ,SUM(ps.used_page_count) AS used
        FROM sys.dm_db_partition_stats ps
        INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
        WHERE it.internal_type IN (
            202
            ,204
            )
        GROUP BY it.parent_id
    ) AS a4 ON (a4.parent_id = a1.object_id)
  INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id)
  INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
  WHERE a2.type <> N''S''
    AND a2.type <> N''IT''
    ';
      DECLARE @request_id nvarchar(36), @schema_for_replace nvarchar(100)
      DECLARE bki_cursor CURSOR FOR
        SELECT name as schem
        FROM    sys.databases
        --Здесь можно перечислить список баз по которым собираем статистику
        /*  where name  in (
          'DWH','DWH_copy','VN','VN_test') --and name ='DWH'
          */
      OPEN bki_cursor
      FETCH NEXT FROM bki_cursor INTO @schema_for_replace
      WHILE @@FETCH_STATUS = 0
      BEGIN
        set @SQL_text_final = replace (@sql_text,'{SCHEMA_FOR_REPLACE}',@schema_for_replace);
        execute (@SQL_text_final)
      FETCH NEXT FROM bki_cursor INTO @schema_for_replace
      END
    CLOSE bki_cursor;
    DEALLOCATE bki_cursor;
END

--Статистика места  в DWH по таблицам
select top 10 ddate -- [Дата]
    ,run_id --
    ,db_name --БД-
    ,schema_name --Схема
    ,table_name --Имя таблицы
    ,row_count --Число записей
    ,round(cast(reserved_KB as float) /1024/1024,2) as  reserved_GB --Зарезервировано (КБ)
    ,round(cast(data_KB as float) /1024/1024,2) as data_GB --Данные (КБ)
    ,round(cast(index_size_KB as float) /1024/1024,2) as index_size_GB --Индексы (КБ)
    ,round(cast(unused_KB as float) /1024/1024,2) as unused_GB--Не используется (КБ)
from  lemon.prm.dwh_size_of_tables
where ddate = cast(getdate() as date)-- and  db_name='DWH'
order by reserved_GB desc

--Статистика места  в DWH по  Базам
select ddate -- [Дата]
    ,run_id --
    ,db_name --БД-
    ,round(cast(sum(reserved_KB) as float) /1024/1024,2) as  reserved_GB --Зарезервировано (КБ)
    ,round(cast(sum(data_KB) as float) /1024/1024,2) as data_GB --Данные (КБ)
    ,round(cast(sum(index_size_KB) as float) /1024/1024,2) as index_size_GB --Индексы (КБ)
    ,round(cast(sum(unused_KB) as float) /1024/1024,2) as unused_GB--Не используется (КБ)
    ,sum(row_count) row_count--Число записей
from  lemon.prm.dwh_size_of_tables
where ddate = cast(getdate() as date)-- and  db_name='DWH'
group by   ddate,run_id,db_name
order  by  ddate,run_id  ,sum(data_KB+index_size_KB) desc

USE [LEMON]
GO
/****** Object:  StoredProcedure [prm].[dwh_daily_size_statistics]    Script Date: 02.09.2020 18:35:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  procedure [prm].[dwh_daily_size_statistics]   @sdate date, @edate date
AS
BEGIN
  --Собираем подневную статистику
    declare   @str nvarchar(4000)
    set @str=
        stuff
        (
          (
          select  N','+ 'round(cast(sum(case when ddate =  cast('''+ cast( ddate as nvarchar)+'''as date)  then  reserved_KB  end) as float) /1024/1024,0)  ['+ cast( ddate as nvarchar)+']'+char(10)
          from (
            select distinct ddate from  lemon.prm.dwh_size_of_tables
            where ddate >=@sdate and  ddate<@edate
            ) t
          order by t.ddate
           for xml path('')
          ,type
          ).value('.','nvarchar(max)'),
          1,0,''
        )-- column_string
    --print @str
    exec ('
    select db_name --БД-
        '+@str+'
    from  lemon.prm.dwh_size_of_tables
    --where ddate = cast(getdate() as date)
    group by  db_name
    --order  by  db_name
    ');
end ;
GO

USE [LEMON]
GO
/****** Object:  StoredProcedure [prm].[dwh_monthly_size_statistics]    Script Date: 02.09.2020 18:35:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [prm].[dwh_monthly_size_statistics]   @sdate date, @edate date
AS
begin
--Собираем помесячую статистику
declare   @str2 nvarchar(4000)
set @str2=
    stuff
    (
      (
      select  N','+ 'round(cast(sum(case when ddate =  cast('''+ cast( ddate as nvarchar)+'''as date)  then  reserved_KB  end) as float) /1024/1024,0)  ['+
      CAST(year( ddate) as nvarchar) +'_'+ CAST(month( ddate) as nvarchar)
      --cast( ddate as nvarchar)
      +']'+char(10)
      from (
        select distinct ddate from  lemon.prm.dwh_size_of_tables
        where ddate >=@sdate and  ddate<@edate and day(ddate)=1
        ) t
      order by t.ddate
       for xml path('')
      ,type
      ).value('.','nvarchar(max)'),
      1,0,''
    )
exec ('
select db_name --БД-
    --,table_name
    '+@str2+'
from  lemon.prm.dwh_size_of_tables
--where ddate = cast(getdate() as date)
group by  db_name--,table_name
order  by  db_name
');
end;
GO

USE [LEMON]
GO
/****** Object:  StoredProcedure [prm].[dwh_monthly_table_size_statistics]    Script Date: 02.09.2020 18:36:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [prm].[dwh_monthly_table_size_statistics]   @sdate date, @edate date ,@db_name nvarchar(100)
AS
begin
--Собираем помесячую статистику
declare   @str2 nvarchar(4000)
set @str2=
    stuff
    (
      (
      select  N','+ 'round(cast(sum(case when ddate =  cast('''+ cast( ddate as nvarchar)+'''as date)  then  reserved_KB  end) as float) /1024/1024,0)  ['+
      CAST(year( ddate) as nvarchar) +'_'+ CAST(month( ddate) as nvarchar)
      --cast( ddate as nvarchar)
      +']'+char(10)
      from (
        select distinct ddate from  lemon.prm.dwh_size_of_tables
        where ddate >=@sdate and  ddate<@edate and day(ddate)=1
        ) t
      order by t.ddate
       for xml path('')
      ,type
      ).value('.','nvarchar(max)'),
      1,0,''
    )
declare @ORDER_DATE NVARCHAR(100)
SET @ORDER_DATE= convert(nvarchar, year( @edate)  ) +'_'+  convert(nvarchar, month( @edate) )
SELECT  @ORDER_DATE = convert(nvarchar, year( DDATE)  ) +'_'+  convert(nvarchar, month( DDATE) ) FROM (
  select MAX( ddate ) DDATE from  lemon.prm.dwh_size_of_tables
        where ddate >=@sdate and  ddate<@edate and day(ddate)=1
        ) tt  ;
declare @ddb_name nvarchar(100)
set @ddb_name =  case when @db_name is null then '' else  ' and '+ 'db_name= '''+@db_name + '''' end
exec ('
select db_name --БД-
    ,table_name
    '+@str2+'
from  lemon.prm.dwh_size_of_tables
where 1=1  ' + @ddb_name  + '
-- ddate = cast(getdate() as date)
group by  db_name,table_name
order by  db_name,['+ @ORDER_DATE +'] desc
');
end;

--Дневная статистика места по базам указываем период  за который смотрим
exec  LEMON.prm.dwh_daily_size_statistics @sdate ='2020-08-01', @edate ='2020-09-01'
--Месячная статистика места по базам указываем период  за который смотрим
exec  LEMON.prm.dwh_monthly_size_statistics @sdate ='2020-03-01', @edate ='2020-09-01'
--Месячная статистика места по каждой таблице
exec  LEMON.prm.dwh_monthly_table_size_statistics
        @sdate ='2020-02-01'
      , @edate ='2020-08-01'
      , @db_name ='DWH'--если указываем null то показывает все таблицы по всем базам