SQL Server: Find Available Space in Data & Log files for all DBs in an instance

SET ANSI_NULLS ON;

SET ARITHABORT ON;

SET QUOTED_IDENTIFIER ON;

SET NOCOUNT ON;

Declare @dbname varchar(max)

DECLARE @command nvarchar(4000);

DECLARE db_names CURSOR FOR SELECT name FROM sys.databases;

open db_names;

fetch next from db_names into @dbname;

while @@FETCH_STATUS = 0

Begin

fetch next from db_names into @dbname

Set @command = N'Use [' + @dbname +'];select @@servername; select DB_NAME(); SELECT name AS [File Name] , physical_name AS [Physical Name], size/128000.0 AS [Total Size in GB],

size/128000.0 - CAST(FILEPROPERTY(name, '''+'SpaceUsed'+''') AS int)/128000.0 AS [Available Space In GB]

FROM sys.database_files WITH (NOLOCK) OPTION (RECOMPILE);';

execute (@command);

--print N'Executed: ' + @command

--select DB_NAME()

--SELECT name AS [File Name] , physical_name AS [Physical Name], size/128000.0 AS [Total Size in GB],

--size/128000.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128000.0 AS [Available Space In GB], [file_id]

--FROM sys.database_files WITH (NOLOCK) OPTION (RECOMPILE);

End

close db_names;

deallocate db_names;

Go