blocks|key|904157|text|尝试这个脚本-它将列出数据库中所有表的行数和数据行使用的空间(以及使用的总空间)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|904158|SELECT+
+t.NAME+AS+TableName,
+i.name+AS+indexName,
+SUM(p.rows)+AS+RowCounts,
+SUM(a.total_pages)+AS+TotalPages,+
+SUM(a.used_pages)+AS+UsedPages,+
+SUM(a.data_pages)+AS+DataPages,
+(SUM(a.total_pages)+*+8)+/+1024+AS+TotalSpaceMB,+
+(SUM(a.used_pages)+*+8)+/+1024+AS+UsedSpaceMB,+
+(SUM(a.data_pages)+*+8)+/+1024+AS+DataSpaceMB
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
WHERE+
+t.NAME+NOT+LIKE+'dt%25'+AND
+i.OBJECT_ID+>+255+AND++
+i.index_id+<=+1
GROUP+BY+
+t.NAME,+i.object_id,+i.index_id,+i.name+
ORDER+BY+
+OBJECT_NAME(i.object_id)+|code-block|syntax|javascript|904159|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this script - it will list the number of rows and the space used by data rows (and the total space used) for all tables in your database:

<pre><code>SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
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
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID &gt; 255 AND 
 i.index_id &lt;= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 
</code></pre>

blocks|key|1118987|text|使用sp_spacedUsed|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1118988|Exec+sp_spaceused+N'YourTableName'|style|CODE|1118989|或者，如果您想为数据库中的每个表执行sp_spaceused，则可以使用下面的SQL：|1118990|set+nocount+on
create+table+#spaceused+(
++name+nvarchar(120),
++rows+char(11),
++reserved+varchar(18),
++data+varchar(18),
++index_size+varchar(18),
++unused+varchar(18)
)

declare+Tables+cursor+for
++select+name
++from+sysobjects+where+type='U'
++order+by+name+asc

OPEN+Tables
DECLARE+@table+varchar(128)

FETCH+NEXT+FROM+Tables+INTO+@table

WHILE+@@FETCH_STATUS+=+0
BEGIN
++insert+into+#spaceused+exec+sp_spaceused+@table
++FETCH+NEXT+FROM+Tables+INTO+@table
END

CLOSE+Tables
DEALLOCATE+Tables+

select+*+from+#spaceused
drop+table+#spaceused

exec+sp_spaceused|code-block|syntax|javascript|1118991|上面的SQL来自here。|1118992|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188776.aspx|1|http://www.cryer.co.uk/brian/sqlserver/howto_list_table_sizes.htm^0|2|D|0|0|0|Y|0|I|C|0|0|8|4|1|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@$A|15|B|16|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|17|8|@$A|18|B|19|F|G]]|9|@]|C|$]]|$1|J|3|K|5|L|7|1A|8|@]|9|@]|C|$M|N]]|$1|O|3|P|5|6|7|1B|8|@]|9|@$A|1C|B|1D|1|1E]]|C|$]]|$1|Q|3|-4|5|6|7|1F|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]]]

Use <a href="http://msdn.microsoft.com/en-us/library/ms188776.aspx" rel="noreferrer">sp_spacedUsed</a>

<code>Exec sp_spaceused N'YourTableName'</code>

Or if you want to execute the <code>sp_spaceused</code> for each table in your database then you can use this SQL:

<pre><code>set nocount on
create table #spaceused (
 name nvarchar(120),
 rows char(11),
 reserved varchar(18),
 data varchar(18),
 index_size varchar(18),
 unused varchar(18)
)

declare Tables cursor for
 select name
 from sysobjects where type='U'
 order by name asc

OPEN Tables
DECLARE @table varchar(128)

FETCH NEXT FROM Tables INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN
 insert into #spaceused exec sp_spaceused @table
 FETCH NEXT FROM Tables INTO @table
END

CLOSE Tables
DEALLOCATE Tables 

select * from #spaceused
drop table #spaceused

exec sp_spaceused
</code></pre>

The above SQL is from <a href="http://www.cryer.co.uk/brian/sqlserver/howto_list_table_sizes.htm" rel="noreferrer">here</a>

blocks|key|904281|text|感谢@marc_s的回答。我需要知道数据与索引空间的关系，所以我继续对查询进行扩展，以包括这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|904282|SELECT+TableName
++++,+SUM(DataRowCounts)+AS+DataRowCounts
++++,+SUM(DataTotalSpaceGB)+AS+DataTotalSpaceGB
++++,+SUM(DataSpaceUsedGB)+AS+DataSpaceUsedGB
++++,+SUM(DataUnusedSpaceGB)+AS+DataUnusedSpaceGB
++++,+SUM(IndexRowCounts)+AS+IndexRowCounts
++++,+SUM(IndexTotalSpaceGB)+AS+IndexTotalSpaceGB
++++,+SUM(IndexSpaceUsedGB)+AS+IndexSpaceUsedGB
++++,+SUM(IndexUnusedSpaceGB)+AS+IndexUnusedSpaceGB
++++,+SUM(DataTotalSpaceGB)+%2B+SUM(IndexTotalSpaceGB)+AS+TotalSpaceGB
FROM
(
SELECT+t.NAME+AS+TableName
++++,+i.type_desc+AS+IndexType
++++,+CASE+WHEN+i.type_desc+IN+('CLUSTERED',+'CLUSTERED+COLUMNSTORE',+'HEAP')+THEN+CAST(ROUND(((SUM(a.total_pages)+*+8)+/+1024.00),+2)/1000+AS+NUMERIC(36,+2))+ELSE+0+END+AS+DataTotalSpaceGB
++++,+CASE+WHEN+i.type_desc+IN+('CLUSTERED',+'CLUSTERED+COLUMNSTORE',+'HEAP')+THEN+CAST(ROUND(((SUM(a.used_pages)+*+8)+/+1024.00),+2)/1000+AS+NUMERIC(36,+2))++ELSE+0+END+AS+DataSpaceUsedGB++++
++++,+CASE+WHEN+i.type_desc+IN+('CLUSTERED',+'CLUSTERED+COLUMNSTORE',+'HEAP')+THEN+CAST(ROUND(((SUM(a.total_pages)+-+SUM(a.used_pages))+*+8)+/+1024.00,+2)/1000+AS+NUMERIC(36,+2))+ELSE+0+END+AS+DataUnusedSpaceGB
++++,+CASE+WHEN+i.type_desc+IN+('CLUSTERED',+'CLUSTERED+COLUMNSTORE',+'HEAP')+THEN+SUM(p.Rows)+ELSE+0+END+AS+DataRowCounts
++++,+CASE+WHEN+i.type_desc+=+'NONCLUSTERED'+THEN+CAST(ROUND(((SUM(a.total_pages)+*+8)+/+1024.00),+2)/1000+AS+NUMERIC(36,+2))+ELSE+0+END+AS+IndexTotalSpaceGB
++++,+CASE+WHEN+i.type_desc+=+'NONCLUSTERED'+THEN+CAST(ROUND(((SUM(a.used_pages)+*+8)+/+1024.00),+2)/1000+AS+NUMERIC(36,+2))++ELSE+0+END+AS+IndexSpaceUsedGB++++
++++,+CASE+WHEN+i.type_desc+=+'NONCLUSTERED'+THEN+CAST(ROUND(((SUM(a.total_pages)+-+SUM(a.used_pages))+*+8)+/+1024.00,+2)/1000+AS+NUMERIC(36,+2))+ELSE+0+END+AS+IndexUnusedSpaceGB++
++++,+CASE+WHEN+i.type_desc+=+'NONCLUSTERED'+THEN+SUM(p.Rows)+ELSE+0+END+AS+IndexRowCounts
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+JOIN+sys.schemas+s+ON+t.schema_id+=+s.schema_id
WHERE+t.NAME+NOT+LIKE+'dt%25'
++++AND+t.is_ms_shipped+=+0
++++AND+i.OBJECT_ID+>+255
++++AND+s.Name+=+'dbo'+--update+this+filter
++++AND+t.Name+=+'MyTable'
GROUP+BY+t.Name
++++,+i.type_desc
)+x
GROUP+BY+TableName
ORDER+BY+TotalSpaceGB+DESC|code-block|syntax|javascript|904283|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Thanks to @marc_s for the answer. I needed to know data vs index space so I went ahead and expanded on the query to include that.

<pre><code>SELECT TableName
 , SUM(DataRowCounts) AS DataRowCounts
 , SUM(DataTotalSpaceGB) AS DataTotalSpaceGB
 , SUM(DataSpaceUsedGB) AS DataSpaceUsedGB
 , SUM(DataUnusedSpaceGB) AS DataUnusedSpaceGB
 , SUM(IndexRowCounts) AS IndexRowCounts
 , SUM(IndexTotalSpaceGB) AS IndexTotalSpaceGB
 , SUM(IndexSpaceUsedGB) AS IndexSpaceUsedGB
 , SUM(IndexUnusedSpaceGB) AS IndexUnusedSpaceGB
 , SUM(DataTotalSpaceGB) + SUM(IndexTotalSpaceGB) AS TotalSpaceGB
FROM
(
SELECT t.NAME AS TableName
 , i.type_desc AS IndexType
 , CASE WHEN i.type_desc IN ('CLUSTERED', 'CLUSTERED COLUMNSTORE', 'HEAP') THEN CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2)/1000 AS NUMERIC(36, 2)) ELSE 0 END AS DataTotalSpaceGB
 , CASE WHEN i.type_desc IN ('CLUSTERED', 'CLUSTERED COLUMNSTORE', 'HEAP') THEN CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2)/1000 AS NUMERIC(36, 2)) ELSE 0 END AS DataSpaceUsedGB 
 , CASE WHEN i.type_desc IN ('CLUSTERED', 'CLUSTERED COLUMNSTORE', 'HEAP') THEN CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2)/1000 AS NUMERIC(36, 2)) ELSE 0 END AS DataUnusedSpaceGB
 , CASE WHEN i.type_desc IN ('CLUSTERED', 'CLUSTERED COLUMNSTORE', 'HEAP') THEN SUM(p.Rows) ELSE 0 END AS DataRowCounts
 , CASE WHEN i.type_desc = 'NONCLUSTERED' THEN CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2)/1000 AS NUMERIC(36, 2)) ELSE 0 END AS IndexTotalSpaceGB
 , CASE WHEN i.type_desc = 'NONCLUSTERED' THEN CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2)/1000 AS NUMERIC(36, 2)) ELSE 0 END AS IndexSpaceUsedGB 
 , CASE WHEN i.type_desc = 'NONCLUSTERED' THEN CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2)/1000 AS NUMERIC(36, 2)) ELSE 0 END AS IndexUnusedSpaceGB 
 , CASE WHEN i.type_desc = 'NONCLUSTERED' THEN SUM(p.Rows) ELSE 0 END AS IndexRowCounts
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 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 &gt; 255
 AND s.Name = 'dbo' --update this filter
 AND t.Name = 'MyTable'
GROUP BY t.Name
 , i.type_desc
) x
GROUP BY TableName
ORDER BY TotalSpaceGB DESC
</code></pre>

How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?
I am sure there is some System Stored Procedure that shows this information.
I have a TEST database that grew from 1tb to 23tb. We are currently doing a lot of client conversion testing in the database, which entails running the same conversion Stored Procedure multiple times. It does DELETEs which I am sure is increasing the Transaction Log. But this got me thinking to ask this question.
<h1>info</h1>
the big problem is the dbo.Download table, it creates massive storage that is actually not needed, I had 3GB before truncating it, then 52MB ;)

How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?

SQL Server

数据库

如何判断哪些表在SQL Server 2005数据库中占用的空间最多？我确信有一些系统存储过程可以显示此信息。我有一个从1tb增长到23tb的测试数据库。我们目前正在数据库中进行大量的客户端转换测试，这需要多次运行相同的转换存储过程。它确实删除了我确信会增加事务日志的内容。但这让我思考了这个问题。信息最大的问题是dbo...

问如何判断哪些表在SQL Server 2005数据库中占用的空间最多？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何判断哪些表在SQL Server 2005数据库中占用的空间最多？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何判断哪些表在SQL Server 2005数据库中占用的空间最多？
EN