SQL Server语句获得数据库及磁盘信息

Question

1.怎样用SQL语句得到Database文件的大小?(mdf/ldf或ndf)
2.怎样用SQL语句得到database文件所在磁盘空间的大小及磁盘空闲空间大小？如同数据名称右键--报表--标准报表--Disk usage   
3.怎样用SQL语句得到数据库中各表的记录数,预留空间大小 数据空间大小 索引空间大小 空闲空间大小？
如同数据名称右键--报表--标准报表--Disk usage by table
4.怎样用SQL语句每个表中记录的条数、预留空间大小、使用空间大小？ 如同数据名称右键--报表--标准报表--Disk usage by Partition谢谢！

Answer 1

1.怎样用SQL语句得到Database文件的大小?(mdf/ldf或ndf)
当前数据库：SELECT * FROM sys.database_files
2.怎样用SQL语句得到database文件所在磁盘空间的大小及磁盘空闲空间大小？如同数据名称右键--报表--标准报表--Disk usage   
xp_fixeddrives其他的暂时没现成脚本

Answer 2

对于这种信息，powershell是不错的选择，不过我不会

Answer 3

-- 1.怎样用SQL语句得到Database文件的大小?(mdf/ldf或ndf)
select file_id,name,type_desc,size*8/1024 'FileSize(MB)' from [数据库名].sys.database_files
-- 2.怎样用SQL语句得到database文件所在磁盘空间的大小及磁盘空闲空间大小？如同数据名称右键--报表--标准报表--Disk usage
select file_id,name,type_desc,size*8/1024 'FileSize(MB)' from [数据库名].sys.database_filesexec xp_fixeddrivesexec xp_cmdshell 'fsutil volume diskfree C:'
-- 3.怎样用SQL语句得到数据库中各表的记录数,预留空间大小 数据空间大小 索引空间大小 空闲空间大小？如同数据名称右键--报表--标准报表--Disk usage by table
exec sp_MSforeachtable "exec sp_spaceused '?'"
 -- 4.怎样用SQL语句每个表中记录的条数、预留空间大小、使用空间大小？ 如同数据名称右键--报表--标准报表--Disk usage by Partition
exec sp_MSforeachtable "exec sp_spaceused '?'"

Answer 4

谢谢各位，执行sp_spaceused时返回两个数据集，请问是否可以把这个两数据集写入两个表中呢？

Answer 5

try this,exec sp_MSforeachtable "insert into [表名]([字段列表]) exec sp_spaceused '?'"

Answer 6

我是说sp_spaceused 时会返回数据库的两个信息，一个是3个字段的记录集，另外一个是4个字段的记录集，怎样把这两条记录写到两个表中？谢谢

Answer 7

--> 据我所知,这个在TSQL中实现不了,需前端程序中用DataSet的Tables[0],Tables[1]属性.

Answer 8

谢谢，知道前台可以做到，哪有怎样的SQL可以得sp_spaceused一样的两个记录集呢？记录集1、database_name,database_size,unallocated space
记录集2、reserved,data,index_size,unused

Answer 9

有怎样的SQL可以得sp_spaceused一样的两个记录集数据呢（字段如下）？
 
记录集1、database_name,database_size,unallocated space
记录集2、reserved,data,index_size,unused 

Answer 10

先建个存储过程：create procedure dbo.proc_spaceused 
@flag int = 1
as  declare @objname nvarchar(776) = nulldeclare @id int   -- The object id that takes up space  
  ,@type character(2) -- The object type.  
  ,@pages bigint   -- Working variable for size calc.  
  ,@dbname sysname  
  ,@dbsize bigint  
  ,@logsize bigint  
  ,@reservedpages  bigint  
  ,@usedpages  bigint  
  ,@rowCount bigint  
  /*  
**  Check to see that the objname is local.  
*/  
if @objname IS NOT NULL  
begin  
  
  select @dbname = db_name()  
  
 /*  
 **  Try to find the object.  
 */  
 SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)  
  
 -- Translate @id to internal-table for queue  
 IF @type = 'SQ'  
  SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue  
  
 /*  
 **  Does the object exist?  
 */  
 if @id is null  
  begin  
   raiserror(15009,-1,-1,@objname,@dbname)  
   return (1)  
  end  
  
 -- Is it a table, view or queue?  
 IF @type NOT IN ('U ','S ','V ','SQ','IT')  
 begin  
  raiserror(15234,-1,-1)  
  return (1)  
 end  
end  
  
  
set nocount on  
  
/*  
**  If @id is null, then we want summary data.  
*/  
if @id is null  
begin  
 select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  
  , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))  
  from dbo.sysfiles  
  
 select @reservedpages = sum(a.total_pages),  
  @usedpages = sum(a.used_pages),  
  @pages = sum(  
    CASE  
     -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"  
     When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  
     When a.type <> 1 Then a.used_pages  
     When p.index_id < 2 Then a.data_pages  
     Else 0  
    END  
   )  
 from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
  left join sys.internal_tables it on p.object_id = it.object_id  
 
  
 /* unallocated space could not be negative */  
 
 
 if @flag = 1
 
 select   
  database_name = db_name(),  
  database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))   
   * 8192 / 1048576,15,2) + ' MB'),  
  'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then  
   (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))   
   * 8192 / 1048576 else 0 end),15,2) + ' MB')  
  
 /*  
 **  Now calculate the summary data.  
 **  reserved: sum(reserved) where indid in (0, 1, 255)  
 ** data: sum(data_pages) + sum(text_used)  
 ** index: sum(used) where indid in (0, 1, 255) - data  
 ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)  
 */
 else  
 select  
  reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),  
  data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),  
  index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),  
  unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')  
end  
  
return (0) -- sp_spaceused  记录集1、database_name,database_size,unallocated space
记录集2、reserved,data,index_size,unused ：declare @db table
(
database_name nvarchar(100),
database_size nvarchar(100),
unallocated_space nvarchar(100)
)
declare @db_data table
(
reserved nvarchar(100),
data nvarchar(100),
index_size nvarchar(100),
unused nvarchar(100)
)insert into @db
exec proc_spaceused @flag = 1  --1表示返回第一个结果集insert into @db_data
exec proc_spaceused @flag = 2   --2表示返回第二个结果集select * from @db
/*
database_name database_size unallocated_space
wcc             6599.13 MB 3998.38 MB
*/select * from @db_data 
/*
reserved data index_size unused
1664 KB     712 KB 808 KB 144 KB
*/