To find all databases data files size and free space

  

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 ORDER BY DBName