An alternative to sp_msforeachdb

Question

​I inherited the script below and struggling to run this against all databases across different instances.

It works when I use sp_msforeachdb but it does not output the way I want it to and it also includes blank result sets for databases with no data which is part of the problem of using sp_msforeachdb...

I feel it's my joins which I have tried different forms but still not outputting the way I want. Kindly review and give feedback.

SET NOCOUNT ON
declare @cmd1 nvarchar(2000)
set @cmd1 =
'use ?;
SELECT ''?''
       DatabaseName,
       [File Name],
       [Physical Name],
       [File - Total Size In MB],
       [File - Available Space In MB],
       [Filegroup Name],
       [Disk Mount Point],
       [Disk Total Size in GB],
       [Disk Available Size in GB],
       [Disk Free Space % ],
       Growth,
       NextGrowthRequirementInMB,
       FileSpaceAfterGrowthMB 
    FROM
       (
          SELECT
             DB_NAME() AS DatabaseName,
             f.[file_id],
             f.name AS [File Name],
             f.physical_name AS [Physical Name],
             CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
             AS [File - Total Size In MB],
             CAST(f.size / 128.0 -
             CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int) 
             / 128.0 AS DECIMAL(15, 2)) 
             AS [File - Available Space In MB],
             fg.name AS [Filegroup Name],
             volume_mount_point [Disk Mount Point],
             CONVERT(DECIMAL(18, 2),
            total_bytes / 1073741824.0) 
            AS [Disk Total Size in GB],
             ---1GB = 1073741824 bytes 
            CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) 
            AS [Disk Available Size in GB], 
             CAST(CAST(available_bytes AS FLOAT) 
            / CAST(total_bytes AS FLOAT) AS DECIMAL(18, 2))
             * 100 AS [Disk Free Space % ],
             f.Growth,
             CASE
                f.is_percent_growth 
                WHEN
                   0 
                THEN
                   f.Growth / 128 
                WHEN
                   1 
                THEN
                   f.growth 
                 / 100.00*CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
             END
             AS NextGrowthRequirementInMB, 
             CONVERT(DECIMAL(18, 2), total_bytes 
             / 1073741824.0) - (
             CASE
                f.is_percent_growth 
                WHEN
                   0 
                THEN
                   f.Growth / 128 
                WHEN
                   1 
                THEN
                   f.growth 
                / 100.00*CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
             END
    ) AS FileSpaceAfterGrowthMB,
  f.is_percent_growth, 
  file_system_type [File System Type] 
          FROM
             sys.database_files AS f WITH (NOLOCK) 
             INNER JOIN
                sys.master_files m 
                on f.file_id = m.file_id 
             LEFT OUTER JOIN
                sys.data_spaces AS fg WITH (NOLOCK) 
                ON f.data_space_id = fg.data_space_id 
        CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id) 
          WHERE
             m.database_id = DB_ID()) a
where DB_NAME() NOT IN(''master'',''model'',''msdb'',
                       ''tempdb'',''xx'',''xxx'',''xxxx'')
     ORDER BY 1 desc'

EXEC sp_foreachdb @command = @cmd1
GO

Answer 1

Remove the semicolon after USE ?; that is why you are getting results always from the same database.

To exclude the system tables, sp_foreachdb already has a parameter named @user_only, you don't need the where at the end

Also, your query is larger than 2000 characters

SET NOCOUNT ON
declare @cmd1 nvarchar(max)
DROP TABLE #TMP
CREATE TABLE #TMP (DatabaseName VARCHAR(100),
       [File Name] VARCHAR(200),
       [Physical Name] VARCHAR(200),
       [File - Total Size In MB] FLOAT,
       [File - Available Space In MB] FLOAT,
       [Filegroup Name] VARCHAR(50),
       [Disk Mount Point]  VARCHAR(50),
       [Disk Total Size in GB] FLOAT,
       [Disk Available Size in GB] FLOAT,
       [Disk Free Space % ] FLOAT,
       Growth FLOAT,
       NextGrowthRequirementInMB FLOAT,
       FileSpaceAfterGrowthMB FLOAT)
set @cmd1 =
'use ?
INSERT #TMP
SELECT 
       DatabaseName,
       [File Name],
       [Physical Name],
       [File - Total Size In MB],
       [File - Available Space In MB],
       [Filegroup Name],
       [Disk Mount Point],
       [Disk Total Size in GB],
       [Disk Available Size in GB],
       [Disk Free Space % ],
       Growth,
       NextGrowthRequirementInMB,
       FileSpaceAfterGrowthMB 
    FROM
       (
          SELECT
             DB_NAME() AS DatabaseName,
             f.[file_id],
             f.name AS [File Name],
             f.physical_name AS [Physical Name],
             CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [File - Total Size In MB],
             CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int) / 128.0 AS DECIMAL(15, 2)) AS [File - Available Space In MB],
             fg.name AS [Filegroup Name],
             volume_mount_point [Disk Mount Point],
             CONVERT(DECIMAL(18, 2), total_bytes / 1073741824.0) AS [Disk Total Size in GB],
             ---1GB = 1073741824 bytes 
             CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Disk Available Size in GB], 
             CAST(CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Disk Free Space % ],
             f.Growth,
             CASE
                f.is_percent_growth 
                WHEN
                   0 
                THEN
                   f.Growth / 128 
                WHEN
                   1 
                THEN
                   f.growth / 100.00*CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
             END
             AS NextGrowthRequirementInMB, CONVERT(DECIMAL(18, 2), total_bytes / 1073741824.0) - (
             CASE
                f.is_percent_growth 
                WHEN
                   0 
                THEN
                   f.Growth / 128 
                WHEN
                   1 
                THEN
                   f.growth / 100.00*CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
             END
    ) AS FileSpaceAfterGrowthMB, f.is_percent_growth, file_system_type [File System Type] 
          FROM
             sys.database_files AS f WITH (NOLOCK) 
             INNER JOIN
                sys.master_files m 
                on f.file_id = m.file_id 
             LEFT OUTER JOIN
                sys.data_spaces AS fg WITH (NOLOCK) 
                ON f.data_space_id = fg.data_space_id CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id) 
          WHERE
             m.database_id = DB_ID()) a' 




     EXEC sp_foreachdb @command = @cmd1, @user_only = 1
     SELECT * 
     FROM #TMP

Answer 2

Below is the script with some additions but now unable to insert the output into a 
table.



SET NOCOUNT ON
declare @cmd1 nvarchar(max)
IF OBJECT_ID('tempdb..#TMP') IS NOT NULL
DROP TABLE #TMP

CREATE TABLE #TMP (
    [CheckDate] datetime,
    [DatabaseName] VARCHAR(100),
   [File Name] VARCHAR(200),
   [Physical Name] VARCHAR(200),
   [File - Total Size In MB] FLOAT,
   [File - Available Space In MB] FLOAT,
   [Filegroup Name] VARCHAR(50),
   [Disk Mount Point]  VARCHAR(50),
   [Disk Total Size in GB] FLOAT,
   [Disk Available Size in GB] FLOAT,
   [Disk Free Space % ] FLOAT,
   Growth FLOAT,
   NextGrowthRequirementInMB FLOAT,
   FileSpaceAfterGrowthMB FLOAT)
set @cmd1 =
'use ?
INSERT #TMP
SELECT 
    [CheckDate],
   [DatabaseName],
   [File Name],
   [Physical Name],
   [File - Total Size In MB],
   [File - Available Space In MB],
   [Filegroup Name],
   [Disk Mount Point],
   [Disk Total Size in GB],
   [Disk Available Size in GB],
   [Disk Free Space % ],
   Growth,
   NextGrowthRequirementInMB,
   FileSpaceAfterGrowthMB 
FROM
   (
      SELECT
CONVERT(VARCHAR(24),GETDATE(),121) [CheckDate],
         DB_NAME() AS DatabaseName,
         f.[file_id],
         f.name AS [File Name],
         f.physical_name AS [Physical Name],
         CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [File - Total Size In MB],
         CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int) / 
128.0 AS DECIMAL(15, 2)) AS [File - Available Space In MB],
         fg.name AS [Filegroup Name],
         volume_mount_point [Disk Mount Point],
         CONVERT(DECIMAL(18, 2), total_bytes / 1073741824.0) AS [Disk Total Size in 
GB],
         ---1GB = 1073741824 bytes 
         CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Disk Available Size 
in GB], 
         CAST(CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) AS 
DECIMAL(18, 2)) * 100 AS [Disk Free Space % ],
         f.Growth,
         CASE
            f.is_percent_growth 
            WHEN
               0 
            THEN
               f.Growth / 128 
            WHEN
               1 
            THEN
               f.growth / 100.00*CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
         END
         AS NextGrowthRequirementInMB, CONVERT(DECIMAL(18, 2), total_bytes / 
1073741824.0) - (
         CASE
            f.is_percent_growth 
            WHEN
               0 
            THEN
               f.Growth / 128 
            WHEN
               1 
            THEN
               f.growth / 100.00*CAST((f.size / 128.0) AS DECIMAL(15, 2)) 
         END
) AS FileSpaceAfterGrowthMB, f.is_percent_growth, file_system_type [File System Type] 
      FROM
         sys.database_files AS f WITH (NOLOCK) 
         INNER JOIN
            sys.master_files m 
            on f.file_id = m.file_id 
         LEFT OUTER JOIN
            sys.data_spaces AS fg WITH (NOLOCK) 
            ON f.data_space_id = fg.data_space_id CROSS APPLY 
sys.dm_os_volume_stats(database_id, m.file_id) 
      WHERE
         m.database_id = DB_ID()) a
ORDER BY 7 desc' 

 EXEC sp_foreachdb @command = @cmd1, @user_only = 1, @exclude_list = '', 
@is_read_only =
NULL
SET NOCOUNT OFF
 SELECT * FROM #TMP