Cool Tips‎ > ‎Data/Database Size‎ > ‎

Data/Database Size - Get Database Size and Database Object Counts

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        27 Aug 2009

Description

This SP retrieves a list of all active databases on a server along with database size (data and log combined) and the count of the number of code objects of the foloowing types in each database
  • Stored Proceudures
  • Tabels
  • Views
  • Users
  • User Defined Functions (scalar and table valued)
This SP was originally part of an enterprise database metadata gaterhing and reporting solution that I wrote, but is the only piece of code that operates without the need for the metadata storage tables. 

Code

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


ċ
Get All DB sizes and object counts per db.sql
(4k)
Andy Hughes,
Jun 19, 2012, 5:53 AM
Comments