LISTING 1: The usp_get_dbstats Stored Procedure

IF EXISTS (SELECT * FROM sysobjects WHERE id =
 object_id(N'[dbo].[usp_get_dbstats]') AND 
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_get_dbstats]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE PROCEDURE usp_get_dbstats AS

DECLARE @DBSTATS_DB char(3)
SET @DBSTATS_DB = 'DBA'

-- Begin callout A
PRINT 'DECLARE @cmd nvarchar(1024) '

PRINT 'IF EXISTS (SELECT * FROM tempdb..sysobjects
 WHERE id = object_id(N' + char(39) + 
'[tempdb]..[#tmplg]' + char(39) + '))'
   PRINT 'DROP TABLE #tmplg'

PRINT 'CREATE TABLE #tmplg'
PRINT '('
PRINT 'DBName varchar(32),'
PRINT 'LogSize real,'
PRINT 'LogSpaceUsed real,'
PRINT 'Status int'
PRINT ')'

PRINT 'SELECT @cmd = ' + char(39) + 'dbcc sqlperf (logspace)' + char(39)

PRINT  'INSERT INTO #tmplg EXECUTE (@cmd)'
-- End callout A
-- Begin callout B
PRINT 'IF EXISTS (SELECT * FROM tempdb..sysobjects
 WHERE id = object_id(N' + char (39) + 
'[tempdb]..[#tmp_stats]' + char(39 ) + '))'
   PRINT 'DROP TABLE #tmp_stats'

PRINT 'CREATE TABLE #tmp_stats ('
PRINT 'totalextents int, '
PRINT 'usedextents int,'
PRINT 'dbname varchar(40),'
PRINT 'logsize real,'
PRINT 'logspaceused real'
PRINT ')'
PRINT 'go'--End callout B
--Begin callout C
DECLARE AllDatabases CURSOR FOR

SELECT name FROM master..sysdatabases
 
OPEN AllDatabases

DECLARE @DB nvarchar(128)

FETCH NEXT FROM AllDatabases INTO @DB

WHILE (@@FETCH_STATUS = 0)

BEGIN
   PRINT 'USE [' + @DB + ']'
   PRINT 'GO'
   PRINT 'IF EXISTS (SELECT * FROM tempdb..sysobjects
 WHERE id = object_id(N' + char(39) 
+ '[tempdb]..[#tmp_sfs]' + char(39) + '))'
   PRINT 'DROP TABLE #tmp_sfs'
   PRINT 'CREATE TABLE #tmp_sfs ('
   PRINT 'fileid int,'
   PRINT 'filegroup int, '
   PRINT 'totalextents int, '
   PRINT 'usedextents int,'
   PRINT 'name varchar(1024),'
   PRINT 'filename varchar(1024)'
   PRINT ')'
   PRINT 'go'

   PRINT 'DECLARE @cmd nvarchar(1024)'

   PRINT 'SET @cmd=' + char(39) + 'DBCC SHOWFILESTATS' + char(39)

   PRINT 'INSERT INTO #tmp_sfs EXECUTE(@cmd)'

   PRINT 'DECLARE @logsize real '
   PRINT 'DECLARE @logspaceused real '

   PRINT 'SELECT @logsize= logsize FROM #tmplg 
WHERE dbname = ' + char(39) + @DB + 
char(39)
   PRINT 'SELECT @logspaceused = (logsize*logspaceused)/100.0'
   PRINT '      FROM #tmplg WHERE dbname = ' + char(39) + @DB + char(39)
   PRINT 'SET @cmd = ' + char(39) + ' INSERT INTO #tmp_stats' + char(39) + ' +'
   PRINT '     ' + char(39) + '(totalextents,usedextents,
dbname,logsize,logspaceused)' + char(39) + 
' +'
   PRINT '     ' + char(39) + ' SELECT SUM(totalextents),
 SUM (usedextents),' + char(39) + ' + 
char(39) + ' + char(39) + @DB + char(39) + '+ char(39) + '
 + char(39) + ',' + char(39) + ' + '
   PRINT ' CAST(@logsize AS varchar) + ' + char(39) + ','
 + char(39) + ' + CAST (@logspaceused 
AS varchar) +'
   PRINT ' ' + char(39) + ' FROM #tmp_sfs' + char(39)
PRINT 'EXEC sp_executesql @cmd'

FETCH NEXT FROM AllDatabases INTO @DB
END --(@@FETCH_STATUS = 0)
--End callout C
--Begin callout D
PRINT 'INSERT INTO ' + @DBSTATS_DB + '.dbo.DBSTATS '
PRINT '  (RECORD_TYPE, DBNAME, DATA_SIZE, DATA_USED, LOG_SIZE, LOG_USED)'
PRINT '    SELECT 1,dbname,totalextents*64/1024 , usedextents*64/1024 ,'
PRINT '           logsize ,logspaceused FROM #tmp_stats'
--End callout D

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO