SQL 如何在SQL中找到表的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3606366/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find the size of a table in SQL?
提问by anjum
How to find the size of a table in SQL?
如何在SQL中找到表的大小？
回答by Kashif
SQL Server:-
SQL 服务器：-
sp_spaceused 'TableName'
Or in management studio: Right Click on table -> Properties -> Storage
或在管理工作室： 右键单击表 -> 属性 -> 存储
MySQL:-
MySQL：-
SELECT table_schema, table_name, data_length, index_length FROM information_schema.tables
Sybase:-
赛贝：-
sp_spaceused 'TableName'
回答by Mark Meuer
Combining the answers from ratty's and Haim's posts (including comments) I've come up with this, which for SQL Server seems to be the most elegant so far:
结合来自ratty和Haim的帖子（包括评论）的答案，我想出了这个，对于SQL Server来说，到目前为止似乎是最优雅的：
-- DROP TABLE #tmpTableSizes
CREATE TABLE #tmpTableSizes
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
insert #tmpTableSizes
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
select * from #tmpTableSizes
order by cast(LEFT(reservedSize, LEN(reservedSize) - 4) as int) desc
This gives you a list of all your tables in order of reserved size, ordered from largest to smallest.
这为您提供了所有表的列表，按照保留大小的顺序，从最大到最小排序。
回答by Developer Marius ?il?nas
A query (modification of https://stackoverflow.com/a/7892349/1737819) to find a custom name table size in GB. You might try this, replace 'YourTableName' with the name of your table.
查询（修改https://stackoverflow.com/a/7892349/1737819）以查找以 GB 为单位的自定义名称表大小。你可以试试这个，用你的表名替换'YourTableName'。
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
CONVERT(DECIMAL,SUM(a.total_pages)) * 8 / 1024 / 1024 AS TotalSpaceGB,
SUM(a.used_pages) * 8 / 1024 / 1024 AS UsedSpaceGB ,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 / 1024 AS UnusedSpaceGB
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 = 'YourTable'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceGB DESC, t.Name
回答by Sameer
SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes
SQL Server 提供了一个内置的存储过程，您可以运行它来轻松显示表的大小，包括索引的大小
sp_spaceused ‘Tablename'
回答by Haim Evgi
SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you.
SQL Server 提供了一个内置的存储过程，您可以运行它来轻松显示表的大小，包括索引的大小……这可能会让您感到惊讶。
Syntax:
句法：
sp_spaceused ‘Tablename'
see in :
见：
http://www.howtogeek.com/howto/database/determine-size-of-a-table-in-sql-server/
http://www.howtogeek.com/howto/database/determine-size-of-a-table-in-sql-server/
回答by mwittrock
Do you by size mean the number of records in the table, by any chance? In that case:
你的大小是指表中的记录数吗？在这种情况下：
SELECT COUNT(*) FROM your_table_name
回答by Justin Clarke
I know that in SQL 2012 (may work in other versions) you can do the following:
我知道在 SQL 2012（可能适用于其他版本）中，您可以执行以下操作：
- Right click on the database name in the Object Explorer.
- Select Reports > Standard Reports > Disk Usage by Top Tables.
- 在对象资源管理器中右键单击数据库名称。
- 选择“报告”>“标准报告”>“按顶级表划分的磁盘使用情况”。
That will give you a list of the top 1000 tables and then you can order it by data size etc.
这将为您提供前 1000 个表的列表，然后您可以按数据大小等对其进行排序。
回答by Frank Heikens
And in PostgreSQL:
在 PostgreSQL 中：
SELECT pg_size_pretty(pg_relation_size('tablename'));
回答by Andrew Walker
Here's a simple query, if you are just trying to find the largest tables.
这是一个简单的查询，如果您只是想找到最大的表。
-- Find largest table partitions
SELECT top 20 obj.name, LTRIM (STR ( sz.in_row_data_page_count * 8, 15, 0) + ' KB') as Size, * FROM sys.dm_db_partition_stats sz
inner join sys.objects obj on obj.object_id = sz.object_id
order by sz.in_row_data_page_count desc
回答by Kairan
SQL Server, nicely formatted table for all tables in KB/MB:
SQL Server，以 KB/MB 为单位的所有表格式良好的表：
SELECT
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 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
t.Name
