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