SQL Server语句获得数据库及磁盘信息
1.怎样用SQL语句得到Database文件的大小?(mdf/ldf或ndf)
2.怎样用SQL语句得到database文件所在磁盘空间的大小及磁盘空闲空间大小？如同数据名称右键--报表--标准报表--Disk usage
3.怎样用SQL语句得到数据库中各表的记录数,预留空间大小 数据空间大小 索引空间大小 空闲空间大小？
如同数据名称右键--报表--标准报表--Disk usage by table
4.怎样用SQL语句每个表中记录的条数、预留空间大小、使用空间大小？ 如同数据名称右键--报表--标准报表--Disk usage by Partition
谢谢！
[解决办法]
1.怎样用SQL语句得到Database文件的大小?(mdf/ldf或ndf)
当前数据库：SELECT * FROM sys.database_files
2.怎样用SQL语句得到database文件所在磁盘空间的大小及磁盘空闲空间大小？如同数据名称右键--报表--标准报表--Disk usage
xp_fixeddrives其他的暂时没现成脚本
[解决办法]
-- 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_files
exec xp_fixeddrives
exec 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 '?'"
create procedure dbo.proc_spaceused
@flag int = 1
as
declare @objname nvarchar(776) = null
declare @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
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_namedatabase_sizeunallocated_space
wcc 6599.13 MB3998.38 MB
*/
select * from @db_data
/*
reserveddataindex_sizeunused
1664 KB 712 KB808 KB144 KB
*/
