摘要：
SUM（a.total_pages）*8ASTotalSpaceKB，CAST（圆形（（（a.total _pages（a.otal_pages）*8）/1024.00），SUM（a.used_pages）*8ASUsedSpaceKB，CASD（圆形（，
转自：https://blog.csdn.net/yenange/article/details/50493580
查询数据文件与日志文件占用情况，查看数据大小，查看库大小
1. 查看数据文件占用（权限要求较大）
DBCC showfilestats
2. 查看日志文件占用dbcc sqlperf(logspace)
USE master go
--简易版
SELECT Name, physical_name, Size/128.0 AS [Size(MB)], FILEPROPERTY(Name,'SpaceUsed')/128.0 AS [SpaceUsed(MB)], STR(FILEPROPERTY(Name,'SpaceUsed')*1.0/Size*100,6,3) AS [SpaceUsed(%)] FROM master.sys.database_files
--详细版
SELECT a.name [文件名称] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)] , CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)] , CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [所占空间率%] , CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN '增量为固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示' ELSE '文件大小固定，不会增长' END AS [增量模式] , CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB' WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%' ELSE '文件大小固定，不会增长' end AS [增长值(%或MB)] , a.physical_name AS [文件所在目录] ,a.type_desc AS [文件类型] FROM sys.database_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]
基于T-SQL：
SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB 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 总共占用空间 desc
基于存储过程--主要原理：
exec sp_spaceused '表名' --取得表占用空間
exec sp_spaceused ''--數據庫所有空間
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))
declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #data
exec sp_spaceused @name
print @name
fetch next from cur into @name
end
close cur
deallocate cur
create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)
insert into #dataNew
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data
select * from #dataNew order by data desc 查看索引大小
--查看索引大小 如果您想要表的每个索引的大小，请使用以下两个查询中的一个： SELECT i.name AS IndexName, SUM(s.used_page_count) * 8 AS 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行的
select * 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 server定时做的统计操作，执行下面代码可进一步精确
DBCC UpdateUSAGE(DatabaseName,[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 BY b.rows DESC