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
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