Get Database Size and Database Object Counts

SET NOCOUNT ON

DROP PROCEDURE usp_GetDatabaseSizeandObjectCounts

GO

CREATE PROCEDURE usp_GetDatabaseSizeandObjectCounts

AS

/***************************************************************

Putpose: To retrieve a list of databases, database sizes

and objcet counts (#SP, #Views etc) for all active

databases on a server

Author: ChillyDBA

History: 27 Aug 2009 - Iniital Issue

****************************************************************/

DECLARE @name SYSNAME,

@cmd VARCHAR(1024),

@dbsize INT

CREATE TABLE #Results

(

dbname SYSNAME,

database_size FLOAT,

unallocated_space FLOAT

)

CREATE TABLE #Users (DBName SYSNAME, Users INT)

CREATE TABLE #SPs (DBName SYSNAME, SPs INT)

CREATE TABLE #Views (DBName SYSNAME, Views INT)

CREATE TABLE #Tables (DBName SYSNAME, Tables INT)

CREATE TABLE #UDFs (DBName SYSNAME, UDFs INT)

-- iterate through all databases

SELECT @name = MIN(name)

FROM MASTER..sysdatabases

WHERE status &amp; 32 <> 32 -- not loading

AND status &amp; 512 <> 512 -- not offline

AND dbid > 4 -- not master, model, msdb or tempdb

WHILE @name IS NOT NULL

BEGIN

-- construct a query string for this database

SELECT @cmd = ''

-- declare local variables

SELECT @cmd = @cmd +

'DECLARE @bytesperpage INT, @pagesperMB INT, @dbsize INT' + CHAR(13)

-- get the absolute size of this database

SELECT @cmd = @cmd +

'SELECT @dbsize = sum(CONVERT(dec(15),size))

FROM [' + @name + '].dbo.sysfiles' + CHAR(13)

-- get bytes per page info FROM master database

SELECT @cmd = @cmd +

'SELECT @bytesperpage = low

FROM master.dbo.spt_values

WHERE number = 1

AND type = ' + '''' + 'E' + '''' + CHAR(13)

-- calculate pages pper mb

SELECT @cmd = @cmd +

'SELECT @pagesperMB = 1048576 / @bytesperpage' + CHAR(13)

-- now get the database size and unallocated space using information in the local

-- sysindexes table

SELECT @cmd = @cmd +

'SELECT database_name = ' + '''' + @name + '''' + ',

database_size = LTRIM(STR(@dbsize / @pagesperMB,15,2)), ' + '''' +

'unallocated space' + '''' + ' =LTRIM(STR((@dbsize - (SELECT SUM(CONVERT(DEC(15),reserved))

FROM [' + @name + '].dbo.sysindexes

WHERE indid IN (0, 1, 255))) / @pagesperMB,15,2))' + CHAR(13)

-- run the command an store it for retrieval

INSERT #Results

EXEC (@cmd)

SELECT @Cmd = 'SELECT ''' + @name + ''' AS DBName, COUNT(*) AS tables FROM [' + @name + '].dbo.sysobjects WHERE type = ''u'''

INSERT #Tables EXEC(@cmd)

SELECT @Cmd = 'SELECT ''' + @name + ''' AS DBName, COUNT(*) AS views FROM [' + @name + '].dbo.sysobjects WHERE type = ''v'''

INSERT #Views EXEC(@cmd)

SELECT @Cmd = 'SELECT ''' + @name + ''' AS DBName, COUNT(*) AS sps FROM [' + @name + '].dbo.sysobjects WHERE type = ''p'''

INSERT #SPs EXEC(@cmd)

SELECT @Cmd = 'SELECT ''' + @name + ''' AS DBName, COUNT(*) AS udfs FROM [' + @name + '].dbo.sysobjects WHERE type in (''fn'', ''tf'')'

INSERT #UDFs EXEC(@cmd)

SELECT @Cmd = 'SELECT ''' + @name + ''' AS DBName, COUNT(*) AS users FROM [' + @name + '].dbo.sysusers WHERE status > 0 and uid > 1'

INSERT #Users EXEC(@cmd)

-- get the next database to be processed

SELECT @name = MIN(name)

FROM MASTER..sysdatabases

WHERE name > @name

AND status &amp; 32 <> 32 -- not loading

AND status &amp; 512 <> 512 -- not offline

AND dbid > 4 -- not master, model, msdb or tempdb

END

-- get all results for the server

SELECT

CONVERT(VARCHAR(30), UPPER(@@SERVERNAME)) AS Server_Name,

CONVERT(VARCHAR(30), UPPER(r.dbname)) AS Database_Name,

CONVERT(DECIMAL(10,2), database_size) AS Database_Size_MB,

t.Tables AS No_Of_Tables,

s.SPs AS No_Of_SPs,

v.Views AS No_Of_Views,

u.UDFs AS No_Of_UDFs,

usr.Users AS No_Of_Users

FROM #Results r

INNER JOIN #Tables t

ON r.DBName = t.DBName

INNER JOIN ##SPs s

ON r.DBName = s.DBName

INNER JOIN #Views v

ON r.DBName = v.DBName

INNER JOIN #UDFs u

ON r.DBName = u.DBName

INNER JOIN #Users usr

ON r.DBName = usr.DBName

ORDER BY Server_Name, r.DBName

DROP TABLE #Results

DROP TABLE #Users

DROP TABLE #Views

DROP TABLE #SPs

DROP TABLE #Tables

DROP TABLE #UDFs