-- Try this.  It'll go through all the DBs if no name is specified, otherwise, just -- return it for the DB specified.

-- 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 = '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)

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