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 & 32 <> 32 -- not loading AND status & 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 & 32 <> 32 -- not loading AND status & 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
|