Return a List of All Dabases with their Storage Amounts

USE [master]
GO

IF NOT EXISTS(SELECT schema_id FROM sys.schemas WHERE name = 'DBA')
BEGIN 
DECLARE @SQLcmd NVARCHAR(50) = 'CREATE SCHEMA [DBA] AUTHORIZATION [db_owner]';
EXEC sp_executesql @SQLcmd;
END 

IF NOT EXISTS(SELECT object_id FROM sys.tables WHERE name ='tDataDiskUsageHistory')
BEGIN
CREATE TABLE DBA.tDataDiskUsageHistory
(
DatabaseName nvarchar(50) NOT NULL,
DateTested date NOT NULL,
TimeTested time(7) NOT NULL,
ReservedDbSpace decimal(12, 2) NULL,
UsedDbSpace decimal(12, 2) NULL,
FreeSpacePercent decimal(12, 2) NULL,
LogSpaceUsed decimal(12, 2) NULL
)  ON [PRIMARY];

ALTER TABLE DBA.tDataDiskUsageHistory ADD CONSTRAINT
PK_tDataDiskUsageHistory PRIMARY KEY CLUSTERED
(
DatabaseName,
DateTested,
TimeTested
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

END
GO

USE [master]
GO

CREATE PROCEDURE DBA.DataDiskUsage_Record_AllDatabases
(@DaysToRetain int = 0)
AS

DECLARE @loop INT;
DECLARE @count INT;
DECLARE @db_name NVARCHAR(50);
DECLARE @exec_string NVARCHAR(1000);
DECLARE @id INT;
DECLARE @free_space INT;

--Create tabel to hold database list
DECLARE @current_databases TABLE
(
 id INT IDENTITY(1,1)
 , name nvarchar(50)
);

--create a table for holding all database free space
DECLARE @space_used TABLE
(
id INT IDENTITY(1,1)
, database_name NVARCHAR(50)
, reserved_db_space DECIMAL(12,2)
, used_db_space DECIMAL(12,2)
, free_space_percent   DECIMAL(5,3)
, log_space_used DECIMAL(12,2)
);

--get the names of all user databases
INSERT INTO @current_databases
SELECT name
FROM sys.databases
WHERE database_id &gt 4 --eliminate system databases
AND snapshot_isolation_state = 0; --eliminate snapshots

SET @loop = 1;
SELECT @count = MAX(id) FROM @current_databases;

--loop through each database and get the data file free space    
WHILE @loop <= @count
 BEGIN

 --get our working db
 SELECT @db_name = name
 FROM @current_databases
 WHERE id = @loop;

 SET @exec_string = 'USE ' + @db_name + ';

  DECLARE @DataSpaceReserved DECIMAL(12,2) = 0.0;
  DECLARE @DataSpaceUsed DECIMAL(12,2) = 0.0;
  DECLARE @LogSpaceUsed DECIMAL(12,2) = 0.0;

  SELECT
  @LogSpaceUsed = SUM(f.size/128.0)
  FROM sys.sysfiles f
  WHERE groupid = 0;-- log files

  SELECT
  @DataSpaceReserved = SUM(f.size/128.0)
  ,  @DataSpaceUsed = SUM(CAST(FILEPROPERTY(f.name,  ''spaceused'') AS int)/128.0)
  FROM sys.sysfiles f
  WHERE groupid != 0;-- data files

  SELECT
  ''' + @db_name + ''' as database_name
  , @DataSpaceReserved
  , @DataSpaceUsed
  , 100 * (@DataSpaceReserved - @DataSpaceUsed)/@DataSpaceReserved
  , @LogSpaceUsed';

print @exec_string;

 --pull our space data back and insert into our holding table
 INSERT @space_used
 EXECUTE (@exec_string);

 --next please            
 SET @loop = @loop + 1

END


INSERT INTO [DBA].[tDataDiskUsageHistory]
           ([DatabaseName]
           ,[DateTested]
           ,[TimeTested]
           ,[ReservedDbSpace]
           ,[UsedDbSpace]
           ,[FreeSpacePercent]
           ,[LogSpaceUsed])
SELECT
database_name
,convert (date ,getdate()) AS QueryDate
,convert (time ,getdate()) AS QueryTime
, reserved_db_space
, used_db_space
, free_space_percent
, log_space_used
FROM @space_used;

IF @DaysToRetain > 0
BEGIN
DELETE FROM DBA.tDataDiskUsageHistory WHERE DATEDIFF(d, DateTested, GETDATE()) > @DaysToRetain
END