Листинг 1.

Хранимая процедура usp_get_dbstats.

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