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;