select SUM(size * 8.0 / 1024) as [Size, Mb]
from sys.master_files
WHERE database_id = DB_ID()
AND type_desc = 'ROWS'
GROUP BY database_id

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

FILEPROPERTY(df.name, 'SpaceUsed')

   USE [master]
   GO
   DECLARE @estimatedBackSizeMB INT
   DECLARE @estimatedDriveFreeSpaceMB INT
   DECLARE @dbCheckMessage varchar(80)
   DECLARE @enoughSpaceForBackupFlag INT
   DECLARE @drvLetter VARCHAR (5)
   SET NOCOUNT ON
   set @drvLetter = 'C'
   SET @dbCheckMessage = concat ('Checking database ', DB_NAME ())
   PRINT @dbCheckMessage

   SELECT @estimatedBackSizeMB = round (sum (a.total_pages) * 8192 / SQUARE (1024.0), 0)
   FROM sys.partitions p
   JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
   LEFT JOIN sys.internal_tables it
      ON p.object_id = it.object_id

   CREATE TABLE #freespace (drive VARCHAR (5), MBFree DECIMAL (8, 2))

   INSERT INTO #freespace (
      Drive,
      MBFree) EXEC xp_fixeddrives

   SELECT @estimatedDriveFreeSpaceMB = MBFree 
   FROM #freespace
   WHERE drive = @drvLetter

   IF @estimatedBackSizeMB * 1.15 < @estimatedDriveFreeSpaceMB
      SET @enoughSpaceForBackupFlag = 1
   ELSE
      SET @enoughSpaceForBackupFlag = 0
/*
   SELECT DatabaseName = db_name(),	
      Estimated_Back_Size_MB = @estimatedBackSizeMB,
      Estimated_Drive_Free_Space_MB = @estimatedDriveFreeSpaceMB,
      EnoughSpaceForBackupFlag = @enoughSpaceForBackupFlag
*/
   DROP TABLE #freespace

   IF @enoughSpaceForBackupFlag = 1
   BEGIN
   PRINT 'Continue to Backup...'
   DECLARE @path NVARCHAR(256); -- Path for backup files
   DECLARE @fileName NVARCHAR(256); -- Filename for backup
   DECLARE @fileDate NVARCHAR(20); -- Used for file name
   DECLARE @name NVARCHAR(50); -- Database name

   -- Build the path and file name.
    SET @path = 'C:\Temp\'
	SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    SET @name = DB_NAME ()
    SET @fileName = @path + @name  + '_' + @fileDate + '.BAK'
	  -- Backup the database.
    BACKUP DATABASE @name TO DISK = @fileName WITH  COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
      -- Loop to the next database.
    END
ELSE 
   PRINT 'Drive Space Problem...'



   SET NOCOUNT OFF