貌似在聚集索引的空间计算上有点差异:EXEC sp_spaceused 'tablename' SELECT o.name, rows = MAX(row_count), reserved = SUM(reserved_page_count) * 8, data = SUM(CASE WHEN ps.index_id IN (0,1) THEN used_page_count END) * 8, index_size = SUM(CASE WHEN ps.index_id >= 2 THEN used_page_count END) * 8, unused = (SUM(reserved_page_count) - SUM(used_page_count)) * 8 FROM sys.dm_db_partition_stats ps INNER JOIN sys.objects o ON ps.object_id = o.object_id WHERE o.name = 'tablename' GROUP BY o.name ORDER BY name
--各数据表的空间使用量 CREATE VIEW vDataDestribute AS SELECT TOP 1000 a3.name AS [schemaname], a2.name AS [tablename], a1.rows as row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved(K)], a1.data * 8 AS [data(k)], (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size(k)], (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused(k)], a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) BytesPerRow FROM ( SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN ( SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id ) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ORDER BY [reserved(K)] DESC GOSELECT * FROM vDataDestribute 忘记哪里来的脚本
exec sp_executesql @stmt=N'begin try SELECT TOP 1000 (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1, a3.name AS [schemaname], a2.name AS [tablename], a1.rows as row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, a1.data * 8 AS data, (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size, (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N''S'' and a2.type <> N''IT'' end try begin catch select -100 as l1 , 1 as schemaname , ERROR_NUMBER() as tablename , ERROR_SEVERITY() as row_count , ERROR_STATE() as reserved , ERROR_MESSAGE() as data , 1 as index_size , 1 as unused end catch',@params=N''SQL 2005 报表里面跟出来的
(
NAME VARCHAR(80),
ROWS VARCHAR(80),
RESERVED VARCHAR(80),
DATA VARCHAR(80),
INDEX_SIZE VARCHAR(80),
UNUSED VARCHAR(80)
)GOexec sp_MSforeachtable 'insert into tb_space exec sp_spaceused ''?'''
GO
--UPDATE TB_SPACE SET RESERVED=SUBSTRING(RESERVED,1,LEN(RESERVED)-3)
--GOselect * from TB_SPACE
GODROP TABLE TB_SPACE
如果要去掉KB,黏贴到excel,把KB替换成空格,把值改为数字类型,可以排序看大小,这样更直观。
其他没深入,你自己再改改。
exec sp_MSforeachtable @command1="sp_spaceused '?'" --获得每个表的记录数和容量:
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? " --获得所有的数据库的存储空间:
EXEC sp_MSforeachdb @command1="print '?'",
@command2="sp_spaceused " --检查所有的数据库
EXEC sp_MSforeachdb @command1="print '?'",
@command2="DBCC CHECKDB (?) "
SELECT
o.name,
rows = MAX(row_count),
reserved = SUM(reserved_page_count) * 8,
data = SUM(CASE WHEN ps.index_id IN (0,1) THEN used_page_count END) * 8,
index_size = SUM(CASE WHEN ps.index_id >= 2 THEN used_page_count END) * 8,
unused = (SUM(reserved_page_count) - SUM(used_page_count)) * 8
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.objects o
ON ps.object_id = o.object_id
WHERE o.name = 'tablename'
GROUP BY o.name
ORDER BY name
CREATE VIEW vDataDestribute
AS
SELECT TOP 1000
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved(K)],
a1.data * 8 AS [data(k)],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size(k)],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused(k)],
a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) BytesPerRow
FROM
(
SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(
SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id
) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY [reserved(K)] DESC
GOSELECT * FROM vDataDestribute
忘记哪里来的脚本
(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N''S'' and a2.type <> N''IT''
end try
begin catch
select
-100 as l1
, 1 as schemaname
, ERROR_NUMBER() as tablename
, ERROR_SEVERITY() as row_count
, ERROR_STATE() as reserved
, ERROR_MESSAGE() as data
, 1 as index_size
, 1 as unused
end catch',@params=N''SQL 2005 报表里面跟出来的