求助：在SQLServer中如何获得磁盘总空间值

Question

求助：
在SQLServer中如何获得磁盘总空间值、已用空间值、未用空间值和已用空间百分比先谢谢各位大侠，在线等！

Answer 1

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpaceUsed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpaceUsed]
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
----------------------------------------------------------
/*
Summary: Get Data and Index Space Used
Author: Jakeie Yang
Create Date: Apr/20/2006
Alter  Date: --  
Alter By: --
*/
----------------------------------------------------------
CREATE procedure SpaceUsed 
WITH ENCRYPTION 
as
begin
declare @id       int                  -- The object id of @objname.
declare @type       character(2) -- The object type.
declare @pages       int                  -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage       dec(15,0)
declare @pagesperMB              dec(15,0)
declare @objname nvarchar(776)        -- The object we want size on.
declare @updateusage varchar(5)             -- Param. for specifying that CREATE TABLE #TEMP1
(
       表名              VARCHAR(200) NULL,
       行数               CHAR(11) NULL,
       保留空间        VARCHAR(15) NULL,
       数据使用空间       VARCHAR(15) NULL,
       索引使用空间       VARCHAR(15) NULL,
       未用空间          VARCHAR(15) NULL
)--select @objname='N_dep'                               -- usage info. should be updated.
select @updateusage='false'
/*Create temp tables before any DML to ensure dynamic
**  We need to create a temp table to do the calculation.
**  reserved: sum(reserved) where indid in (0, 1, 255)
**  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
**  indexp: sum(used) where indid in (0, 1, 255) - data
**  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/declare cur_table cursor for select name from sysobjects where type='u'
Open cur_table
fetch next from cur_table into @objname
While @@FETCH_STATUS=0
begin
create table #spt_space
(
        rows       int null,
        reserved    dec(15) null,
        data        dec(15) null,
        indexp      dec(15) null,
        unused      dec(15) null
)
/*
**  Check to see if user wants usages updated.
*/
if @updateusage is not null
        begin
         select @updateusage=lower(@updateusage)
               if @updateusage not in ('true','false')
                begin
                 raiserror(15143,-1,-1,@updateusage)
                        return(1)
               end
        end/*
**  Check to see that the objname is local.
*/ if @objname IS NOT NULL
begin
        select @dbname = parsename(@objname, 3)
        if @dbname is not null and @dbname <> db_name()
               begin
                     raiserror(15250,-1,-1)
                     return (1)
              end
        if @dbname is null
        select @dbname = db_name()
       /*
       **  Try to find the object.
       */       select @id = null
       select @id = id, @type = xtype
       from sysobjects
       where id = object_id(@objname)
       /*
       **  Does the object exist?
       */       if @id is null
       begin
             raiserror(15009,-1,-1,@objname,@dbname)
             return (1)
       end
       if not exists (select * from sysindexes where @id = id and indid < 2)
              if  @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
              begin
                      raiserror(15234,-1,-1)
                    return (1)              end
              else if @type = 'V ' -- View => no physical data storage.
              begin
                      raiserror(15235,-1,-1)
                      return (1)
              end
              else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
              begin
                      raiserror(15064,-1,-1)
                      return (1)
              end
              else if @type = 'F ' -- FK => no physical data storage.
              begin
                        raiserror(15275,-1,-1)
                        return (1)
              end
end /***  Update usages if user specified to do so.*/ if @updateusage = 'true'       begin              if @objname is null                     dbcc updateusage(0) with no_infomsgs              else                     dbcc updateusage(0,@objname) with no_infomsgs              print ' '       end  set nocount on /***  If @id is null, then we want summary data.*//*    Space used calculated in the following way**       @dbsize = Pages used**       @bytesperpage = d.low (where d = master.dbo.spt_values) is**    the # of bytes per page when d.type = 'E' and**       d.number = 1.**    Size = @dbsize * d.low / (1048576 (OR 1 MB))*/if @id is nullbegin       select @dbsize = sum(convert(dec(15),size))              from dbo.sysfiles              where (status & 64 = 0)        select @logsize = sum(convert(dec(15),size))              from dbo.sysfiles              where (status & 64 <> 0)        select @bytesperpage = low              from master.dbo.spt_values              where number = 1                     and type = 'E'       select @pagesperMB = 1048576 / @bytesperpage        select  database_name = db_name(),              database_size =                     ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),              'unallocated space' =                     ltrim(str((@dbsize -                            (select sum(convert(dec(15),reserved))                                   from sysindexes                                          where indid in (0, 1, 255)                            )) / @pagesperMB,15,2)+ ' MB')        print ' '       /*       **  Now calculate the summary data.       **  reserved: sum(reserved) where indid in (0, 1, 255)       */       insert into #spt_space (reserved)              select sum(convert(dec(15),reserved))                     from sysindexes                            where indid in (0, 1, 255)

Answer 2

        /*      ** data: sum(dpages) where indid < 2       **    + sum(used) where indid = 255 (text)       */       select @pages = sum(convert(dec(15),dpages))                     from sysindexes                            where indid < 2       select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)              from sysindexes                     where indid = 255       update #spt_space              set data = @pages         /* index: sum(used) where indid in (0, 1, 255) - data */       update #spt_space              set indexp = (select sum(convert(dec(15),used))                            from sysindexes                                   where indid in (0, 1, 255))                         - data        /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */       update #spt_space              set unused = reserved                            - (select sum(convert(dec(15),used))                                   from sysindexes                                          where indid in (0, 1, 255))        select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +                            ' ' + 'KB'),              data = ltrim(str(data * d.low / 1024.,15,0) +                            ' ' + 'KB'),              index_size = ltrim(str(indexp * d.low / 1024.,15,0) +                            ' ' + 'KB'),              unused = ltrim(str(unused * d.low / 1024.,15,0) +                            ' ' + 'KB')              from #spt_space, master.dbo.spt_values d              where d.number = 1                     and d.type = 'E'end /***  We want a particular object.*/elsebegin       /*       **  Now calculate the summary data.       **  reserved: sum(reserved) where indid in (0, 1, 255)       */       insert into #spt_space (reserved)              select sum(reserved)                     from sysindexes                            where indid in (0, 1, 255)                                   and id = @id        /*      ** data: sum(dpages) where indid < 2       **    + sum(used) where indid = 255 (text)       */       select @pages = sum(dpages)                     from sysindexes                            where indid < 2                                   and id = @id       select @pages = @pages + isnull(sum(used), 0)              from sysindexes                     where indid = 255                            and id = @id       update #spt_space              set data = @pages         /* index: sum(used) where indid in (0, 1, 255) - data */       update #spt_space              set indexp = (select sum(used)                            from sysindexes                                   where indid in (0, 1, 255)                                          and id = @id)                         - data        /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */       update #spt_space              set unused = reserved                            - (select sum(used)                                   from sysindexes                                          where indid in (0, 1, 255)                                                 and id = @id)       update #spt_space
              set rows = i.rows
                     from sysindexes i
                            where i.indid < 2
                                   and i.id = @id
        insert into #temp1
       select name = object_name(@id),
              rows = convert(char(11), rows),
              reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
                            ' ' + 'KB'),
              data = ltrim(str(data * d.low / 1024.,15,0) +
                            ' ' + 'KB'),
              index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
                            ' ' + 'KB'),
              unused = ltrim(str(unused * d.low / 1024.,15,0) +
                            ' ' + 'KB')
       from #spt_space, master.dbo.spt_values d
              where d.number = 1
                     and d.type = 'E'
Drop table #spt_space
end
fetch next from cur_table into @objname
end
Close cur_table
DEALLOCATE cur_table
Select * from #temp1 order by len(数据使用空间) desc,数据使用空间 desc
Drop table #temp1
return (0)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 3

表名                                                                                                                                                                                                       行数          保留空间            数据使用空间          索引使用空间          未用空间
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- --------------- --------------- --------------- ---------------
Log_SA                                                                                                                                                                                                   24009141    4720392 KB      4719744 KB      24 KB           624 KB
TB_Backup_Bom                                                                                                                                                                                            3684477     2292864 KB      1520024 KB      16336 KB        756504 KB

Answer 4

if object_id('tb')is not null drop table tb
go
create table tb(
表名 sysname,
记录数 int,
保留空间 nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间   varchar(10))
exec sp_MSForEachTable @command1=N'insert tb exec sp_spaceused ''?'''
select * from tb

Answer 5

貌似只能exec master..xpcmdshell 'dir C:\'然后放入表中,写一个存储过程去解决了

Answer 6

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1; 
GO 
RECONFIGURE; 
GO 
use master if object_id('P_diskspace_monitor') is not null 
drop procedure P_diskspace_monitor 
go 
CREATE PROCEDURE P_diskspace_monitor 
AS 
SET NOCOUNT ON DECLARE @hr int 
DECLARE @fso int 
DECLARE @drive char(1) 
DECLARE @odrive int 
DECLARE @TotalSize varchar(20) 
DECLARE @MB bigint ; SET @MB = 1048576 CREATE TABLE #drives (drive char(1) PRIMARY KEY, 
FreeSpace int NULL, 
TotalSize int NULL) INSERT #drives(drive,FreeSpace) 
EXEC master.dbo.xp_fixeddrives EXEC @hr=master.sys.sp_OACreate 'Scripting.FileSystemObject',@fso OUT DECLARE dcur CURSOR LOCAL FAST_FORWARD 
FOR SELECT drive from #drives 
ORDER by drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 
BEGIN EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive 
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT 
UPDATE #drives 
SET TotalSize=@TotalSize/@MB 
WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive END CLOSE dcur 
DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso 
SELECT drive, 
FreeSpace as 'Free(MB)', 
TotalSize as 'Total(MB)', 
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' 
FROM #drives 
ORDER BY drive DROP TABLE #drives RETURN 
go大家试试吧，好像不错