TSQL: Get database space info

Generate friendly space used and free data for each database on a server. Replace word 'less-than' with the less than symbol I had to add o get this to post correctly...

CREATE PROC prcDBA_Database_Space_Used @pdbname_filter varchar(100) = '%'
AS
BEGIN

/****CommentStart

Author: Kevin Foreman
Date: 28-02-2007
Version: SQL2000_001.01
Description:
Uses code pilfered from SQL Server 2000 sp_spaceused
and the output of DBCC SQLPERF (LOGSPACE) to generate
a friendly resultset of database total, data and log
space used/free for all DBs.

Only looks at ONLINE databases if all databases queried

RAISERROR if DBNAME FILTER does not match a local DB name
RAISERROR if any negative space values returned


Health Warning:
Frankencode with limited error handling

Inputs: @pdbname_filter : string database name part
Outputs: Resultset
Used By: DBA

Maintained:
01-03-2007 KF added various quotenames to deal with dodgy DB names
such as those starting with a number and not a character
08-03-2007 KF SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid
blocking problems

CommentEnd****/

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--to run as SQL instead of a proc
--uncomment next three lines and run from here to final END
-- DECLARE
-- @pdbname_filter varchar(100)
-- SET @pdbname_filter = '%'

DECLARE
@pdbname sysname ,
@dbsize dec(15,2) ,
@logsize dec(15,2) ,
@bytesperpage dec(15,2) ,
@pagesperMB dec(15,2) ,
@unallocated_space dec(15,2) ,
@sql nvarchar(2000) ,
@s varchar(2000),
@i smallint


--add wildcards to dbname filter
SELECT @pdbname_filter = '%' + @pdbname_filter + '%'

--check dbname_filter
IF NOT EXISTS (SELECT name from master.dbo.sysdatabases WHERE UPPER(name) LIKE (UPPER(@pdbname_filter)))
BEGIN
SELECT @s = 'DBNAME_FILTER: ' + QUOTENAME(REPLACE(@pdbname_filter,'%','')) + ' not valid on this server. Data for ALL databases will be returned.'
RAISERROR (@s,11,1)
SELECT @pdbname_filter = '%'
END

CREATE TABLE ##database_space
(dbname sysname NULL,
dbsize dec(15,2) NULL,
logsize dec(15,2) NULL,
unallocated_space dec(15,2) NULL ,
logused decimal(15,2) NULL
)

CREATE TABLE #log_space
(database_name sysname ,
logsize decimal(15,2),
logused decimal(15,2) ,
status bit)

--get some logspace info
SELECT @sql = 'DBCC SQLPERF(LOGSPACE)'
INSERT #log_space
EXEC (@sql)

--get some sizing info
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'

SELECT @pagesperMB = 1048576 / @bytesperpage

--get spaceused info for each database
WHILE EXISTS
(SELECT name FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name,'status') = 'ONLINE'
AND name LIKE @pdbname_filter
AND name NOT IN (SELECT dbname FROM ##database_space))

BEGIN
SELECT @pdbname = (SELECT TOP 1 name FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name,'status') = 'ONLINE'
AND name LIKE @pdbname_filter
AND name NOT IN (SELECT dbname FROM ##database_space))

SELECT @sql =
'INSERT ##database_space (dbname , dbsize)
SELECT ''' + @pdbname + ''' , SUM(CONVERT(dec(15,2),size))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysfiles]
WHERE (status & 64 = 0)'
EXEC (@sql)

SELECT @sql =
'UPDATE ##database_space SET logsize =
(SELECT SUM(CONVERT(dec(15,2),size))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysfiles]
WHERE (status & 64 <> 0)) WHERE dbname = ''' + @pdbname + ''''
EXEC (@sql)

SELECT @sql =
'UPDATE ##database_space
SET unallocated_space =
(dbsize -
(SELECT SUM(CONVERT(dec(15,2),reserved))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysindexes]
WHERE indid in (0, 1, 255))
) / ' + CONVERT(varchar,@pagesperMB) + '
WHERE dbname = ''' + @pdbname + ''''
EXEC (@sql)
END

--consolidate and tidy up data
UPDATE ##database_space
SET dbsize = (ds.dbsize + ds.logsize) / @pagesperMB ,
logsize = ls.logsize ,
logused = ls.logused
FROM ##database_space AS ds, #log_space AS ls
WHERE ds.dbname = ls.database_name


--check to see if our numbers are valid
SELECT @i = 0
SELECT @s = 'WARNING: '
IF EXISTS
(SELECT * FROM ##database_space WHERE dbsize less-than 0)
SELECT @i = 1 , @s = 'DBSIZE:'
IF EXISTS
(SELECT * FROM ##database_space WHERE logsize less-than 0)
SELECT @i = @i + 1 , @s = @s + 'LOGSIZE:'
IF EXISTS
(SELECT * FROM ##database_space WHERE unallocated_space less-than 0)
SELECT @i = @i + 1 , @s = @s + 'UNALLOCATED_SPACE:'

IF @i > 0
BEGIN
SELECT @s = @s + ' Usage data error. Call a DBA!'
RAISERROR(@s,11,1)
END



--return results
SELECT
dbname AS 'Database_Name',
dbsize AS 'Databse_Size' ,
dbsize - logsize AS 'Data_Size',
logsize AS 'logsize' ,
dbsize - unallocated_space - logsize AS 'Used_Data_Space' ,
unallocated_space AS 'Free_Data_Space' ,
CONVERT(dec(15,2),logsize * logused / 100) AS 'Used_log_Space' ,
CONVERT(dec(15,2),logsize - (logsize * logused / 100)) AS 'Free_log_Space'
FROM
##database_space
ORDER BY
dbname ASC

--tidy up
DROP TABLE ##database_space
DROP TABLE #log_space

END