Finding the free space in the data file of database in SQL Server.

The below query will provide us the amount of free space that exist at database data file level.

CREATE TABLE #FileSize
(dbName NVARCHAR(128), 
    [FileName] NVARCHAR(128), 
	physical_name varchar(150),
    type_desc NVARCHAR(128),
    CurrentSizeMB DECIMAL(10,2), 
    FreeSpaceMB DECIMAL(10,2)
);
INSERT INTO #FileSize
(dbName,[FileName],physical_name,type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb 
'use [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName,
		physical_name, 
        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) and type_desc=''ROWS'';';
SELECT * 
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb') 
and type_desc='ROWS' 
and physical_name LIKE 'E:\%'
--AND FreeSpaceMB > ?;
order by FreeSpaceMB desc
DROP TABLE #FileSize;

IF OBJECT_ID('tempdb..#DBFiles') IS NOT NULL
    DROP TABLE #DBFiles

CREATE TABLE #DBFiles
(
	[id] INT IDENTITY(1,1), 
	[DBName] VARCHAR(200),
	[Recovery] VARCHAR(200) NULL,
	[File_Name] VARCHAR(200) NULL,
	[Type_Desc] VARCHAR(20) NULL,
	[physical_name] VARCHAR(MAX) NULL,
	[FileSize_MB] DECIMAL(10,2) NULL,
	[UsedSpace_MB] DECIMAL(10,2) NULL,
	[FreeSpace_MB] DECIMAL(10,2) NULL,
	[FreeSpace_%] DECIMAL(10,2) NULL,
	[State_Desc] VARCHAR(20) NULL,
	[AutoGrow] VARCHAR(200) NULL
)

INSERT INTO #DBFiles
SELECT 
	db.[name] AS [DBName],
	db.recovery_model_desc,
	files.[name] AS [File_Name],
	files.[type_desc],
	files.physical_name,
	CONVERT(DECIMAL(10,2), files.SIZE/128.0) AS [FILESIZE_MB],
	NULL AS [UsedSpace_MB],
	NULL AS [FreeSpace_MB],
	NULL AS [FreeSpace_%],
	files.state_desc,
	CASE is_percent_growth 
		WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -'
			ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
			ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB'
			END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting! - dbtales.com]'
			ELSE ''
			END AS [AutoGrow]

FROM sys.master_files AS files
INNER JOIN sys.databases AS db
	ON files.database_id = db.database_id
WHERE db.[name] NOT IN ('model','tempdb','msdb','master');

EXEC sp_msforeachdb '
USE [?];

UPDATE #DBFiles
	SET [USEDSPACE_MB] = CONVERT(DECIMAL(10,2), SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(dbf.[name], ''SpaceUsed'') AS INT)/128.0)),
	[FREESPACE_MB] = CONVERT(DECIMAL(10,2), dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0),
	[FREESPACE_%] = CONVERT(DECIMAL(10,2), ((dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0)/(dbf.SIZE/128.0))*100)
FROM #DBFiles AS temp
	INNER JOIN sys.database_files AS dbf 
	ON temp.[physical_name] = dbf.[physical_name] COLLATE DATABASE_DEFAULT '

SELECT * FROM #DBFiles 
where type_desc='rows' 
--and physical_name LIKE 'E:\%'
ORDER BY [FreeSpace_MB] DESC