SQLServer查看库、表占⽤空间⼤⼩
转⾃：blog.csdn/yenange/article/details/50493580
查询数据⽂件与⽇志⽂件占⽤情况，查看数据⼤⼩，查看库⼤⼩
1. 查看数据⽂件占⽤（权限要求较⼤）
DBCC showfilestats
2. 查看⽇志⽂件占⽤
dbcc sqlperf(logspace)
USE master
go
--简易版
SELECT
半年度工作总结
Name,
physical_name,
Size/128.0AS[Size(MB)],
FILEPROPERTY(Name,'SpaceUd')/128.0AS[SpaceUd(MB)],
STR(FILEPROPERTY(Name,'SpaceUd')*1.0/Size*100,6,3) AS[SpaceUd(%)]
匙组词
FROM master.sys.databa_files
--详细版
SELECT a.name [⽂件名称] ,cast(a.[size]*1.0/128as decimal(12,1)) AS[⽂件设置⼤⼩(MB)] ,
CAST( fileproperty(s.name,'SpaceUd')/(8*16.0) AS DECIMAL(12,1)) AS[⽂件所占空间(MB)] ,
CAST( (fileproperty(s.name,'SpaceUd')/(8*16.0))/(s.size/(8*16.0))*100.0AS DECIMAL(12,1)) AS[所占空间率%] ,
CASE wth =0THEN'⽂件⼤⼩固定，不会增长'ELSE'⽂件将⾃动增长'end[增长模式] ,CASE wth >0AND is_percent_growth =0
THEN'增量为固定⼤⼩'wth >0AND is_percent_growth =1THEN'增量将⽤整数百分⽐表⽰'ELSE'⽂件⼤⼩固定，不会增长'END AS[增量模式] ,
CASE wth >0AND is_percent_growth =0THEN cast(wth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'
wth >0AND is_percent_growth =1THEN cast(wth AS decimal(12,0)) AS VARCHAR)+'%'ELSE'⽂件⼤⼩固定，不会增长'end AS[增长值(%或MB)] , a.physical_name AS[⽂件所在⽬录] ,a.type_desc AS[⽂件类型]
FROM sys.databa_files a
细心的近义词INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id]ORDER BY a.[type]
转⾃：/nikyxxx/archive/2012/10/08/2715423.html
sql rver查看所有表⼤⼩、所占空间
基于T-SQL
软件知识产权SELECT
db_name() as DbName,
t.NAME AS TableName,
s.Name AS SchemaName,
al_pages) *8AS TotalSpaceKB,
CAST(ROUND(((al_pages) *8) /1024.00), 2) AS NUMERIC(36, 2)) AS总共占⽤空间MB,
SUM(a.ud_pages) *8AS UdSpaceKB,
CAST(ROUND(((SUM(a.ud_pages) *8) /1024.00), 2) AS NUMERIC(36, 2)) AS UdSpaceMB,
(al_pages) -SUM(a.ud_pages)) *8AS UnudSpaceKB,
CAST(ROUND(((al_pages) -SUM(a.ud_pages)) *8) /1024.00, 2) AS NUMERIC(36, 2)) AS UnudSpaceMB
FROM
sys.tables t宫腔出血
INNER JOIN
sys.indexes i ON t.OBJECT_ID= i.object_id
先行组织者INNER JOIN
sys.partitions p ON i.object_id= p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE'dt%'
AND t.is_ms_shipped =0
AND i.OBJECT_ID>0
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
总共占⽤空间MB desc
基于存储过程（exec sp_spaceud）
--主要原理：
exec sp_spaceud '表名' --取得表占⽤空間
exec sp_spaceud ''--數據庫所有空間
create table #Data(name varchar(100),row varchar(100),rerved varchar(100),data varchar(100),index_size varchar(100),unud varchar(100)) declare @name varchar(100)
declare cur cursor for
lect name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
inrt into #data
银耳的功效和作用exec sp_spaceud @name
print @name
fetch next from cur into @name
end
clo cur
deallocate cur
create table #DataNew(name varchar(100),row int,rerved int,data int,index_size int,unud int)
inrt into #dataNew
lect name,convert(int,row) as row,convert(int,replace(rerved,'KB','')) as rerved,convert(int,replace(data,'KB','')) as data,
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unud,'KB','')) as unud from #data
lect * from #dataNew order by data desc
查看索引⼤⼩
--查看索引⼤⼩
如果您想要表的每个索引的⼤⼩，请使⽤以下两个查询中的⼀个：
SELECT
i.name AS IndexName,
SUM(s.ud_page_count) *8AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id]= i.[object_id]AND s.index_id = i.index_id
WHERE s.[object_id]=object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name
---第2种⽅法
SELECT
i.name AS IndexName,
SUM(page_count *8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
重阳诗JOIN sys.indexes AS i
ON s.[object_id]= i.[object_id]AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
结果通常略有不同，但在1%之内。
查看堆表及数据量超过10W⾏的
lect*from (
SELECT tables.NAME,
(SELECT rows
FROM sys.partitions
WHERE object_id= tables.object_id
AND index_id =0-- 0 is for heap
-- 1 is for clustered index
And rows >=100000
)AS numberofrows
FROM db_tank.sys.tables tables
WHERE Objectproperty(tables.object_id, N'TableHasClustIndex') =0
)t where numberofrows is not null
查看表数据⾏数
但这种办法不是实时的，是sql rver定时做的统计操作，执⾏下⾯代码可进⼀步精确DBCC UpdateUSAGE(DatabaName,[TABLENAME])WITH ROW_COUNTS SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN
sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER ws DESC
