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