sql-server-2005 - 获得所有表的大小

  显示原文与译文双语对照的内容

我继承了一个相当大的SQL Server 数据库。 它似乎占用了比我预期的更多空间,因为它包含的数据。

是否有一个简单的方法来确定每个表占用多少空间?

时间:


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 
 t.Name

如果你使用的是 SQL Server Management Studio,而不是运行查询( 在我的例子中,返回了重复的行),你可以运行一个标准报告。

  1. 右键单击数据库
  2. 导航到报表> 标准按表格报告> 磁盘使用情况

注意:数据库兼容级别必须设置为 900或者上才能正常工作。 参见 http://msdn.microsoft.com/en-gb/library/bb510680.aspx web

sp_spaceused可以获取有关表,索引视图或者整个数据库所使用的磁盘空间的信息。

例如:


USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

这报告了ContactInfo表格的磁盘使用信息。

要同时对所有表使用这里选项:


USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

你还可以从right-click标准报告功能中获取磁盘使用情况。 要到达这里报表,请从对象资源管理器中的服务器对象导航,下移到数据库对象,然后选择任何数据库。 从出现的菜单中,选择报告,然后选择标准报告,然后选择"按分区使用磁盘": [DatabaseName]"。

经过一些搜索之后,我找不到一个简单的方法来获取所有表的信息。 有一个名为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


 exec sp_spaceused N'dbo.MyTable'

上面的查询很适合查找表( 包括的索引) 使用的空间量,但是如果你想比较表中索引使用的空间,请使用这里查询:


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
GROUP BY
 i.OBJECT_ID,
 i.index_id,
 i.name
ORDER BY
 OBJECT_NAME(i.OBJECT_ID),
 i.index_id

于 SQL Server 相关 2005及以上学历;., 如果你需要计算完全相同的数字,那是在火花源质谱法'表属性- 存储'页上,你需要计算它们用同样的手法,它火花源质谱法( works.中完成 对于带有LOB字段的表,也可以正常工作- 因为只计算"used_pages"不是enought来显示准确的索引大小:


;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

请注意 sp_spaceused 使用的表( 在这里帖子的其他查询中提到) 可能不是最新的,参见 MSDN文档

在 SQL Server 当指定updateusage时,数据库引擎会扫描数据库中的数据页,并使任何需求所需要的sys.allocation_units 与 sys.partitions 目录视图对于初存储每个表使用的空间。 某些情况下,在删除索引后,表的空间信息可能不存在。 在大型表或者数据库上运行updateusage可能需要一些时间。 仅当你怀疑返回的值不正确并且进程不会对数据库中的其他用户或者进程产生不利影响时使用 updateusage 。 如果愿意,可以单独运行 DBCC UPDATEUSAGE 。

这将为你提供每个表的大小和记录计数。


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

我的帖子仅与 SQL Server 2000相关,已经被测试为在我的环境中工作。

这段代码会访问 所有可能的数据库的单一实例,而不仅仅是一个单独的数据库。

我使用两个临时表帮助收集适当的数据,然后将结果转储到一个'实时'表中。

返回的数据是:数据库名称,DatabaseTableName,行( 在表格中),数据( 以KB表示的表的大小),条目数据( 我发现这对我最后一次运行脚本很有用) 。

这里代码的缺点是'数据'字段不是作为 int ( 字符字符''保留在该字段中) 存储的,这将是用于排序的有用( 但并非完全必要) 。

希望这个代码能帮助某人,并节省一些时间 !


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

...