我继承了一个相当大的 SQL Server 数据库。考虑到它包含的数据,它似乎占用的空间比我预期的要多。
有没有一种简单的方法可以确定每个表占用了多少磁盘空间?
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
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 > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
如果您使用的是 SQL Server Management Studio (SSMS),则可以运行标准报告,而不是运行查询(在我的情况下它返回重复的行)。
右键单击数据库导航到报告 > 标准报告 > 按表的磁盘使用情况
注意:数据库兼容级别必须设置为 90 或更高才能正常工作。请参阅http://msdn.microsoft.com/en-gb/library/bb510680.aspx
sp_spaceused 可以获得有关表、索引视图或整个数据库使用的磁盘空间的信息。
例如:
USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO
这会报告 ContactInfo 表的磁盘使用信息。
要一次将其用于所有表:
USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO
您还可以从 SQL Server 的右键单击标准报告功能中获取磁盘使用情况。要访问此报告,请从对象资源管理器中的服务器对象导航,向下移动到数据库对象,然后右键单击任何数据库。从出现的菜单中,选择报告,然后选择标准报告,然后选择“按分区划分的磁盘使用情况:[DatabaseName]”。
sp_msforeachtable
很容易触发 System.OutOfMemoryException
,因此使用临时表来存储结果可能是一个更好的主意。
这是另一种方法:使用 SQL Server Management Studio,在对象资源管理器中,转到您的数据库并选择表
https://i.stack.imgur.com/xsAzU.png
然后打开对象资源管理器详细信息(通过按 F7 或转到查看->对象资源管理器详细信息)。在对象资源管理器详细信息页面中,右键单击列标题并启用您希望在页面中看到的列。您也可以按任何列对数据进行排序。
https://i.stack.imgur.com/CTsTn.png
exec sp_spaceused N'dbo.MyTable'
对于所有表格,使用..(从保罗的评论中添加)
exec sp_MSForEachTable 'exec sp_spaceused [?]'
exec sp_helpdb
更改为显示有关表格的任何内容的 exec sp_spaceused
- 但一次只显示一张表格...不会概述您拥有哪些表、它们有多少行以及它们占用了多少空间。
经过一番搜索,我找不到一种简单的方法来获取所有表格的信息。有一个名为 sp_spaceused 的方便存储过程将返回数据库使用的所有空间。如果提供了表名,则返回该表使用的空间。但是,存储过程返回的结果不可排序,因为列是字符值。
以下脚本将生成我正在寻找的信息。
create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes
对于 Azure,我使用了这个:
你应该有 SSMS v17.x+
我用了;
https://i.stack.imgur.com/UupDT.png
有了这个,作为用户 Sparrow has mentioned:
https://i.stack.imgur.com/2UaCf.png
这里的 SSMS 连接到 Azure 数据库
上面的查询非常适合查找表使用的空间量(包括索引),但是如果您想比较表上的索引使用了多少空间,请使用以下查询:
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
i.is_primary_key = 0 -- fix for size discrepancy
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id
where [i].[is_primary_key] = 0
修正了他的答案。现在尺寸应该匹配。
sp_spaceused
匹配。我测量 GB,所以几兆不匹配并不多。我不在乎确切的尺寸,只是一个想法。
使用多个分区和/或过滤索引时,Marc_s 的回答给出了不正确的结果。它也不区分数据和索引的大小,这通常非常相关。一些建议的修复没有解决核心问题,或者也完全是错误的。
以下查询解决了所有这些问题。
SELECT
[object_id] = t.[object_id]
,[schema_name] = s.[name]
,[table_name] = t.[name]
,[index_name] = CASE WHEN i.[type] in (0,1,5) THEN null ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
,[object_type] = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX' END
,[index_type] = i.[type_desc]
,[partition_count] = p.partition_count
,[row_count] = p.[rows]
,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
ELSE ( SELECT DISTINCT p.data_compression_desc
FROM sys.partitions p
WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
)
END
,[total_space_MB] = cast(round(( au.total_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[used_space_MB] = cast(round(( au.used_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[unused_space_MB] = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
FROM sys.partitions
GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table
@xav answer 的扩展,它处理表分区以获取 MB 和 GB 大小。在 SQL Server 2008/2012 上测试(注释了 is_memory_optimized = 1
的行)
SELECT
a2.name AS TableName,
a1.rows as [RowCount],
--(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
--a1.data * 8 AS DataSize_KB,
--(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
--(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
--'| |' Separator_MB_GB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
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
--===Remove the following comment for SQL Server 2014+
--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
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'
--AND a2.name = 'MyTable' --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC
如果您需要计算完全相同的数字,即在 SSMS 中的“表属性 - 存储”页面上,您需要使用与 SSMS 中相同的方法来计算它们(适用于 sql server 2005 及更高版本......对具有 LOB 字段的表正常工作 - 因为仅计算“used_pages”不足以显示准确的索引大小):
;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.TableName,
cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc
WHEN (i.index_id < 2|) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
给出堆的页数吗?在 sql server 2014
中只有一个索引满足此条件(并且该索引是主键:i.index_id = 1)。但是,如果我将大小与 sb_spaceused 的结果进行比较,那么它们匹配(在很大程度上)
由于重复记录,我们使用表分区并且在上面提供的查询中遇到了一些问题。
对于需要此功能的用户,您可以在 SQL Server 2014 生成“按表的磁盘使用情况”报告时运行的查询下方找到。我认为它也适用于以前版本的 SQL Server。
它就像一个魅力。
SELECT
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
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
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 a3.name, a2.name
与 Marc_s's answer 的一个小变化,因为我经常返回此页面,按大多数行的第一个排序:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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 > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
--p.rows DESC --Uncomment to order by amount rows instead of size in KB.
SUM(a.total_pages) DESC
-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
这是一种通过以下步骤快速获取所有表格大小的方法:
编写给定的 T-SQL 命令以列出所有数据库表: select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' 现在复制数据库表列表,并将其复制到新的查询分析器窗口中 exec sp_spaceused table1 exec sp_spaceused table2 exec sp_spaceused table3 exec sp_spaceused table4 exec sp_spaceused table5 在 SQL 查询分析器中,从顶部工具栏选项中选择结果到文件 (Ctrl + Shift + F)。现在终于点击了上面工具栏中标记为红色的执行按钮。所有表的数据库大小现在都存储在您计算机上的一个文件中。
要获取一个数据库中的所有表大小,您可以使用以下查询:
Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
您可以更改它以将所有结果插入临时表,然后从临时表中选择。
Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
Select * from #TempTable
这将为您提供每个表的大小和记录数。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
END
{ }
) 以很好地格式化和语法突出显示它!
从使用 OSQL 的命令提示符:
OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
我在 marc_s 答案的顶部添加了几列:
with fs
as
(
select i.object_id,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKb
from sys.indexes i 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
WHERE
i.OBJECT_ID > 255
GROUP BY
i.object_id,
p.rows
)
SELECT
t.NAME AS TableName,
fs.RowCounts,
fs.TotalSpaceKb,
t.create_date,
t.modify_date,
( select COUNT(1)
from sys.columns c
where c.object_id = t.object_id ) TotalColumns
FROM
sys.tables t INNER JOIN
fs ON t.OBJECT_ID = fs.object_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
ORDER BY
t.Name
我的帖子只与 SQL Server 2000 相关,并且已经过测试可以在我的环境中工作。
此代码访问单个实例的所有可能的数据库,而不仅仅是单个数据库。
我使用两个临时表来帮助收集适当的数据,然后将结果转储到一个“实时”表中。
返回的数据是:DatabaseName、DatabaseTableName、Rows(在表中)、data(看起来以 KB 为单位的表大小)、条目数据(我发现这有助于了解我上次运行脚本的时间)。
此代码的缺点是“数据”字段未存储为 int(字符“KB”保存在该字段中),这对于排序很有用(但并非完全必要)。
希望这段代码可以帮助那里的人并节省他们一些时间!
CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
AS
BEGIN
SET NOCOUNT OFF
CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @SQL nvarchar(4000)
SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '
INSERT INTO #DatabaseTables(DbName, TableName)
EXECUTE sp_msforeachdb @Command1=@SQL
DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR
SELECT TableName FROM #DatabaseTables
DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR
SELECT DBName FROM #DatabaseTables
DECLARE @DBName sysname
OPEN AllDatabaseNames
DECLARE @TName sysname
OPEN AllDatabaseTables
WHILE 1=1 BEGIN
FETCH NEXT FROM AllDatabaseNames INTO @DBName
FETCH NEXT FROM AllDatabaseTables INTO @TName
IF @@FETCH_STATUS<>0 BREAK
INSERT INTO #AllDatabaseTableSizes
EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName)
END
--http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
SELECT [dbname], name, [rows], data FROM #DatabaseTables
INNER JOIN #AllDatabaseTableSizes
ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
GROUP BY [dbname] , name, [rows], data
ORDER BY [dbname]
--To be honest, I have no idea what exact duplicates we are dropping
-- but in my case a near enough approach has been good enough.
DELETE FROM [rsp_DatabaseTableSizes]
WHERE name IN
(
SELECT name
FROM [rsp_DatabaseTableSizes]
GROUP BY name
HAVING COUNT(*) > 1
)
DROP TABLE #DatabaseTables
DROP TABLE #AllDatabaseTableSizes
CLOSE AllDatabaseTables
DEALLOCATE AllDatabaseTables
CLOSE AllDatabaseNames
DEALLOCATE AllDatabaseNames
END
--EXEC [dbo].[usp_getAllDBTableSizes]
如果您需要知道,rsp_DatabaseTableSizes 表是通过以下方式创建的:
CREATE TABLE [dbo].[rsp_DatabaseSizes](
[DatabaseName] [varchar](1000) NULL,
[dbSize] [decimal](15, 2) NULL,
[DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
在上面的@Mark 回答中,添加了@updateusage='true' 以强制使用最新的大小统计信息(https://msdn.microsoft.com/en-us/library/ms188776.aspx):
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' '
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
这是一个示例查询,用于获取按大小降序排列的大于 1GB 的表。
USE YourDB
GO
DECLARE @Mult float = 8
SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs
; WITH CTE AS
(
SELECT
i.object_id,
Rows = MAX(p.rows),
TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),
UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)
FROM
sys.indexes i
JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
i.object_id > 255
GROUP BY
i.object_id
HAVING
SUM(a.total_pages) * @Mult > 1
)
SELECT
SchemaName = s.name,
TableName = t.name,
c.TotalSpaceGB,
c.UsedSpaceGB,
UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,
[RowCount] = c.Rows
FROM
CTE c
JOIN
sys.tables t ON t.object_id = c.object_id
JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
c.TotalSpaceGB DESC
作为对 marc_s's answer(已被接受)的简单扩展,它被调整为返回列数并允许过滤:
SELECT *
FROM
(
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
(SUM(a.used_pages) * 8) AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
INNER JOIN
INFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAME
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 > 255
GROUP BY
t.Name, s.Name, p.Rows
) AS Result
WHERE
RowCounts > 1000
AND ColumnCount > 10
ORDER BY
UsedSpaceKB DESC
我发现此查询易于使用且快速。
select schema_name(tab.schema_id) + '.' + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables (nolock) tab
inner join sys.indexes (nolock) ind
on tab.object_id = ind.object_id
inner join sys.partitions (nolock) part
on ind.object_id = part.object_id and ind.index_id = part.index_id
inner join sys.allocation_units (nolock) spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc
如果您只关心数据库中浪费的空白空间而不关心单个表,则可以考虑以下事项:
如果数据库经历了大量的数据插入然后删除,可能就像在 ETL 情况下,这将导致数据库中过多的未使用空间,因为文件组会自动增长但不会自动收缩。
您可以使用数据库的“属性”页面查看是否是这种情况。您可以缩小(右键单击数据库 > 任务 > 缩小)并收回一些空间。但是,如果根本原因仍然存在,数据库将会重新增长(并且会消耗额外的时间来尝试重新增长并且事情会变慢,直到它增长到足够多 - 所以在这种情况下不要这样做)
https://i.stack.imgur.com/W8gTb.png
也许表格有更多的分区文件&必须显示文件顺序
SELECT
T1.Name AS TableName,
T5.Name AS SchemaName,
T3.partition_number AS PartionNumber,
T3.Rows AS RowsCount,
SUM(T4.total_pages) * 8 AS TotalSpaceKB,
SUM(T4.used_pages) * 8 AS UsedSpaceKB,
(SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.objects T1 INNER JOIN
sys.indexes T2 ON T1.object_id = T2.object_id INNER JOIN
sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id INNER JOIN
sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
T1.type='U'
GROUP BY
T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
T1.Name,T3.partition_number;
CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed
(
TableName sysname
,NumRows BIGINT
,ReservedSpace VARCHAR(50)
,DataSpace VARCHAR(50)
,IndexSize VARCHAR(50)
,UnusedSpace VARCHAR(50)
)
insert into #tmp_table_info
select s.name+'.'+t.name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';
declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin
declare @tablename varchar(200)
select @tablename=tblname
from #tmp_table_info
where id =@min
DECLARE @str VARCHAR(500)
SET @str = 'sp_spaceused '''+@tablename+''''
INSERT INTO #SpaceUsed
EXEC (@str)
set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed
不定期副业成功案例分享
AND i.filter_definition IS NULL
的 where 条件。