Mikel
来源： SQL Server查看库、表占用空间大小 – 郭大侠1 – 博客园
查询数据文件与日志文件占用情况，查看数据大小，查看库大小
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]
转自：https://www.cnblogs.com/nikyxxx/archive/2012/10/08/2715423.html
SQL server查看所有表大小、所占空间
基于T-SQL
SELECT
db_name() as DbName,
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 总共占用空间MB,
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
总共占用空间MB desc
基于存储过程（exec sp_spaceused）
–主要原理：
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
