check DataBase allocated and unused size

SET NOCOUNT ON ;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
-- create temporary table that will store data space used information
CREATE TABLE ##tmp_sfs
   (
    fileid INT
   ,filegroup INT
   ,totalextents INT
   ,usedextents INT
   ,name VARCHAR(1024)
   ,filename VARCHAR(1024)
   ,DBName VARCHAR(128)
   ) ;       
  
-- Declare variables
DECLARE @CMD VARCHAR(2000) ;
-- Command to gather space for each database
SET @CMD = 'DECLARE @DBName varchar(128);
SET @DBName = ''?'';
INSERT INTO ##tmp_sfs (fileid,filegroup,totalextents,
usedextents,name,filename)
EXEC (''USE ['' + @DBName + '']
DBCC SHOWFILESTATS WITH NO_INFOMSGS'');
UPDATE ##tmp_sfs SET DBName = @DBName WHERE DBName is NULL' ;
-- Run command against each database
EXEC master.sys.sp_MSforeachdb @CMD ;
SELECT   DBName
        ,[DataAllocMB]
        ,[DataUsedMB]
        ,[DataAllocMB] - [DataUsedMB] AS [DataUnUsedMB]
        ,[LOG File(s) Size (KB)] / 1024.0 AS [LogAllocMB]
INTO     #Capacity
FROM     ( SELECT instance_name AS DBName
                 ,cntr_value
                 ,counter_name
           FROM   sys.dm_os_performance_counters
           WHERE  counter_name IN ( 'Log File(s) Size (KB)' )
                  AND instance_name NOT IN ( '_Total' , 'mssqlsystemresource' )
           UNION ALL
           SELECT DBname
                 ,totalextents * 8 / 128.0 AS cntr_value
                 ,'DataAllocMB' AS counter_name
           FROM   ##tmp_sfs
           UNION ALL
           SELECT DBname
                 ,usedextents * 8 / 128.0 AS cntr_value
                 ,'DataUsedMB' AS counter_name
           FROM   ##tmp_sfs ) AS PerfCounters PIVOT ( SUM(cntr_value) FOR counter_name IN ( [LOG File(s) Size (KB)] , [DataAllocMB] , [DataUsedMB] ) ) AS pvt ;

--- Total Capacity
SELECT   @@SERVERNAME AS ServerName
        ,COUNT(DBName) AS DBCount
        ,SUM(DataAllocMB) AS Allocated_TotalData_MB
        ,SUM(DataUsedMB) AS Used_TotalData_MB
        ,SUM(DataUnUsedMB) AS UnUsed_TotalData_MB
        ,SUM(LogAllocMB) AS Allocated_TotalLog_MB
        ,SUM(DataAllocMB) + SUM(LogAllocMB) AS Tatal_MB
FROM     #Capacity

--- Total Capacity GB
SELECT   @@SERVERNAME AS ServerName
        ,COUNT(DBName) AS DBCount
        ,SUM(DataAllocMB)/1024 AS Allocated_TotalData_GB
        ,SUM(DataUsedMB)/1024 AS Used_TotalData_GB
        ,SUM(DataUnUsedMB)/1024 AS UnUsed_TotalData_GB
        ,SUM(LogAllocMB)/1024 AS Allocated_TotalLog_GB
        ,(SUM(DataAllocMB) + SUM(LogAllocMB)) / 1024 AS Tatal_GB
FROM     #Capacity
---  Capacity Per Data Base
SELECT   DBName
        ,[DataAllocMB] AS AllocatedData_MB
        ,[DataUsedMB] AS UsedData_MB
        ,[DataUnUsedMB] AS UnUsedData_MB
        ,[LogAllocMB] AS AllocatedLog_MB
FROM     #Capacity
ORDER BY [DataAllocMB] DESC

-- drop temporary table
DROP TABLE ##tmp_sfs
DROP TABLE #Capacity ;
GO