Opps. Forgot to change the DB between cursor lookups. Edited to work on case sensitive collations, too. Try this one instead....
-- Wayne Fairless, DBA
USE
MASTER
GO
IF
(SELECT COUNT(*) FROM dbo.sysobjects WHERE NAME = N'proc_ShowAvailableSpace') = 1
BEGIN
DROP PROCEDURE dbo.proc_ShowAvailableSpace
END
GO
CREATE
PROCEDURE dbo.proc_ShowAvailableSpace
@DBName
NVARCHAR(256) = NULL
AS
SET
NOCOUNT ON
DECLARE
@TSQL NVARCHAR(512),
@RETCODE
INT,
@CurrentDB VARCHAR
(256)
IF
@DBName IS NULL
BEGIN
DECLARE DB_CUR CURSOR
FOR
SELECT NAME FROM master.dbo.sysdatabases
ORDER BY NAME
OPEN DB_CUR
FETCH NEXT FROM DB_CUR INTO @CurrentDB
PRINT @CURRENTDB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL = 'USE ' + @CurrentDB+ '; SELECT name AS [File Name (' + @CurrentDB + ')], ' +
'size/128.0 -CAST(FILEPROPERTY(name, ' +
CHAR(39) + 'SpaceUsed' + CHAR(39) +
')AS int)/128.0 AS [Available Space (MB)] ' +
'FROM [' + @CurrentDB + '].[dbo].[sysfiles]'
EXEC (@TSQL)
--print @tsql
FETCH NEXT FROM DB_CUR INTO @CurrentDB
END
CLOSE DB_CUR
DEALLOCATE DB_CUR
END
ELSE
BEGIN
PRINT 'Free space for database "' + @DBName + '":'
-- Check to see if the DB exists
IF (SELECT DB_ID(@DBName)) IS NULL
BEGIN
PRINT @DBName + 'does not exist.'
RETURN
END
ELSE
BEGIN
SET @TSQL = 'SELECT name AS [File Name], ' +
'size/128.0 -CAST(FILEPROPERTY(name, ' +
CHAR(39) + 'SpaceUsed' + CHAR(39) +
')AS int)/128.0 AS [Available Space (MB)] ' +
'FROM [' + @DBName + '].[dbo].[sysfiles]'
EXEC (@TSQL)
END
END
-- EXEC dbo.proc_ShowAvailableSpace 'master'
-- EXEC dbo.proc_ShowAvailableSpace