Chris Harshman (10/12/2016)

---

I'm sorry, I forgot this was a custom stored proc. It's basically doing the sp_foreachdb loop outside of the remote procedure call:

CREATE PROCEDURE dbo.sp_all_db_filespace AS

BEGIN

CREATE TABLE #FileStats([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_grow

th] bit, [state_desc] varchar(60));

EXECUTE sp_msforeachdb 'USE [?]; INSERT INTO #FileStats SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(40)), LEFT(DB_NAME(),40), CAST(DateDiff(day, 0, GETDATE()) AS DateTime), f.file_id, LEFT(f.name,60), f.type_desc, LEFT(f.physical_name,1), f.

size/128, FILEPROPERTY(f.name, ''SpaceUsed'')/128, f.size/128 - FILEPROPERTY(f.name, ''SpaceUsed'')/128, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END, f.is_percent_growth, f.state_desc FROM sys.database_files f';

SELECT * FROM #FileStats;

DROP TABLE #FileStats

END