Листинг 3.

Пример использования хранимой процедуры.

declare @cmd nvarchar(1024) 
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmplg]?))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(32),
LogSize real,
LogSpaceUsed real,
Status int
)
SELECT @cmd = ?dbcc sqlperf (logspace)?
INSERT INTO #Tmplg EXECUTE (@cmd)
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_stats]?))
drop table #tmp_stats
create table #tmp_stats (
totalextents int, 
usedextents int,
dbname varchar(40),
logsize real,
logspaceused real
)
go
use [dba]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?dba?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?dba?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? + 
char(39) + ?dba?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
use [master]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?master?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?master?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? +
 char(39) + ?master?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
use [model]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?model?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?model?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? + 
char(39) + ?model?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
use [msdb]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?msdb?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?msdb?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? + 
char(39) + ?msdb?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
use [Northwind]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?Northwind?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?Northwind?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? +
 char(39) + ?Northwind?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
use [pubs]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?pubs?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?pubs?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? + 
char(39) + ?pubs?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
use [tempdb]
go
if exists (select * from tempdb..sysobjects where id =
 object_id(N?[tempdb]..[#tmp_sfs]?))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int, 
totalextents int, 
usedextents int,
name varchar(1024),
filename varchar(1024)
)
go
declare @cmd nvarchar(1024)
set @cmd=?DBCC SHOWFILESTATS?
insert into #tmp_sfs execute(@cmd)
declare @logsize real 
declare @logspaceused real 
select @logsize= logsize from #tmplg where dbname = ?tempdb?
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = ?tempdb?
set @cmd = ?insert into #tmp_stats? +
     ?(totalextents,usedextents,dbname,logsize,logspaceused)? +
     ? select sum(totalextents), sum(usedextents),? +
 char(39) + ?tempdb?+ char(39) + ?,? + 
 cast(@logsize as varchar) + ?,? + cast(@logspaceused as varchar) +
 ? from #tmp_sfs?
exec sp_executesql @cmd
INSERT INTO dba.dbo.DBSTATS 
  (RECORD_TYPE, DBNAME, DATA_SIZE, DATA_USED, LOG_SIZE, LOG_USED)
    SELECT 1,dbname,totalextents*64/1024 , usedextents*64/1024 ,
           logsize ,logspaceused from #tmp_stats