/*============================================================================= 

Fetch total db size and list every tables size in a selected list of dbs

Parameters
----------------------


Context
----------------------
in all pharmacies and central

Creation : 24.12.2024 / TSC
Modifications:

=============================================================================*/ 

--#region db list
DECLARE @q NVARCHAR(MAX);
DECLARE @dbFilter TABLE(dbName VARCHAR(255)NOT null);
INSERT INTO @dbFilter
(
    [dbName]
)
SELECT d.[name]
FROM sys.databases d
WHERE d.[name] LIKE 'activePos%'
OR 
d.[name] IN (
    'arizona'
    ,'arizonaCust'
    ,'arizonaLd'
)

IF OBJECT_ID('tempdb..#tblSize')IS NOT NULL BEGIN;
	DROP TABLE #tblSize;
END;

IF OBJECT_ID('tempdb..#dbs')IS NOT NULL BEGIN
    DROP TABLE #dbs;
END

CREATE TABLE #tblSize (
    [serverName] NVARCHAR(128)
  , [dbName] NVARCHAR(128)
  , [schemaName] NVARCHAR(128)
  , [TableName] NVARCHAR(128)
  , [RowCounts] BIGINT
  , [TotalSpaceMB] NUMERIC(26,6)
  , [UsedSpaceMB] NUMERIC(26,6)
  , [DataSpaceMB] NUMERIC(26,6)
  , compressCurrentSpaceMB NUMERIC(26,6)
  , compressEstimateSpaceMB NUMERIC(26,6)
)
CREATE TABLE #dbs (
    [database_name] NVARCHAR(128) NOT NULL
  , [log_size_mb] NUMERIC(18,2) NOT NULL
  , [row_size_mb] NUMERIC(18,2) NOT NULL
  , [total_size_mb] NUMERIC(18,2) NOT NULL
);

INSERT INTO [#dbs] ([database_name], [log_size_mb], [row_size_mb], [total_size_mb])
SELECT 
      [database_name] = DB_NAME(f.[database_id])
    , log_size_mb = CAST(SUM(CASE WHEN f.[type_desc] = 'LOG' THEN [F].[size] ELSE 0 END) * 8. / 1024 AS DECIMAL(18,2))
    , row_size_mb = CAST(SUM(CASE WHEN f.[type_desc] = 'ROWS' THEN [F].[size] ELSE 0 END) * 8. / 1024 AS DECIMAL(18,2))
    , total_size_mb = CAST(SUM([F].[size]) * 8. / 1024 AS DECIMAL(18,2))
FROM [sys].[master_files] F 
    JOIN @dbFilter df ON df.[dbName] = DB_NAME(f.[database_id])
GROUP BY F.[database_id]
ORDER BY [total_size_mb] DESC; 
--#endregion db list

--#region db size
IF OBJECT_ID('tempdb..#FileSize')IS NOT NULL BEGIN;
	DROP TABLE #FileSize;
END;
CREATE TABLE #FileSize(
    [dbname] NVARCHAR(128) NOT NULL, 
    [filename] NVARCHAR(128) NOT NULL, 
    [type_desc] NVARCHAR(128) NOT NULL,
    CurrentSizeMB DECIMAL(10,2) NOT NULL, 
    FreeSpaceMB DECIMAL(10,2) NULL
);
INSERT INTO #FileSize([dbname], [filename], [type_desc], CurrentSizeMB, FreeSpaceMB)
EXECUTE sp_msforeachdb 
'use [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName, 
        type_desc,
        size/128.0 AS CurrentSizeMB,  
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);';
--#endregion db size

--#region db size
SELECT 
    d.[name] AS [dbname]
    ,f.[physical_name] AS physicalFileName
    ,f.[name] AS logicalFileName
    ,CAST(f.SIZE * 8. / 1024 AS DECIMAL(18,2)) AS fileSizeMB
    ,fs.[FreeSpaceMB] AS FreeSpaceInFileMB
    ,fs.[CurrentSizeMB] - fs.[FreeSpaceMB] AS SpaceUsedMB
    ,CAST((fs.[CurrentSizeMB] - fs.[FreeSpaceMB]) * 100 / (CAST(f.SIZE * 8. / 1024 AS DECIMAL(18,2))) AS NUMERIC(6,2)) AS fileSpaceUsedPercent
    ,d2.[total_size_mb] AS totalDbSizeMB
FROM [sys].[master_files] F 
    JOIN [sys].[databases] d ON d.[database_id] = f.[database_id]
    JOIN [#dbs] [d2] ON d2.[database_name] = d.[name]
    LEFT JOIN [#FileSize] fs ON fs.[FileName] = f.[name] AND fs.[dbName] = d.[name]
WHERE f.[type_desc] IN (
    'ROWS'  --mdf
)
ORDER BY [d].[name] DESC, f.[name] ASC ;
--#endregion db size

--#region tables list
SET @q ='';
DECLARE @tplTbl NVARCHAR(MAX)='
USE @db@

INSERT INTO #tblSize(
    [serverName],
    [dbName],
    [schemaName] ,
    [TableName] ,
    [RowCounts] ,
    [TotalSpaceMB] ,
    [UsedSpaceMB] ,
    [DataSpaceMB],
    [compressCurrentSpaceMB],
    [compressEstimateSpaceMB]
)
SELECT      @@SERVERNAME as serverName,
            DB_NAME() as dbName,
            SCHEMA_NAME(t.schema_id) as schemaName,
            t.name AS TableName,
            MAX([p].[rows]) AS RowCounts,
            (SUM([a].[total_pages]) * 8) / 1024.0 AS TotalSpaceMB,
            (SUM([a].[used_pages]) * 8) / 1024.0 AS UsedSpaceMB,
            (SUM([a].[data_pages]) * 8) / 1024.0 AS DataSpaceMB,
            MAX(ce.[current_size_KB])/1024 AS compressCurrentSpaceMB,   --MAX() because you can have more than 1 index on the table
            MAX(ce.[estimated_compressed_size_in_kb])/1024 AS compressEstimateSpaceMB
  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 JOIN [HCITools].[tmp].[compression_estimate] ce
    ON ce.[database_name] = db_name()
    AND ce.[schema_name] = SCHEMA_NAME(t.schema_id)
    AND ce.[table_name] = t.[name]
 WHERE      i.object_id > 255
   --AND      i.index_id IN ( 0, 1 )
 GROUP BY t.name, t.schema_id
 ORDER BY TotalSpaceMB DESC;

';
SELECT @q = @q + REPLACE(@tplTbl, '@db@', [dbName])
FROM @dbFilter;

--PRINT @q
EXEC [sys].[sp_executesql]
    @q
    ,N''
;
SELECT * 
FROM [#tblSize]
--#endregion tables list