如何在SQL中查找表的大小?
SQL Server:-
sp_spaceused 'TableName'
或者在管理工具中:
右键点击表格 → 属性 → 存储MySQL:-
SELECT table_schema, table_name, data_length, index_length FROM information_schema.tables
Sybase:-
sp_spaceused 'TableName'
Oracle:- 如何在Oracle中计算表的大小
data_length
是表的大小(以字节为单位),而不是行数。请参阅:https://dev.mysql.com/doc/refman/8.0/en/tables-table.html - Ceasar在超过10年之后进行更新(使用评论中的建议改进版本):
这将按保留大小顺序列出您所有的表,从最大到最小。
此方法与先前的方法相同,但它使结果表成为一个变量,因此在完成时不需要清理它。同时将所有值转换为实际整数,以便可以轻松查看和排序。
DECLARE @tmpTableSizes TABLE
(
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
tableName,
CAST(numberofRows AS INT) 'numberOfRows',
CAST(LEFT(reservedSize, LEN(reservedSize) - 3) AS INT) 'reservedSize KB',
CAST(LEFT(dataSize, LEN(dataSize) - 3) AS INT) 'dataSize KB',
CAST(LEFT(indexSize, LEN(indexSize) - 3) AS INT) 'indexSize KB',
CAST(LEFT(unusedSize, LEN(unusedSize) - 3) AS INT) 'unusedSize KB'
FROM
@tmpTableSizes
ORDER BY
[reservedSize KB] DESC
2023-02-21 之前的旧版:
结合 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
这将按保留大小顺序列出所有表格的列表,从最大到最小排序。
SQL Server提供了一个内置的存储过程,可以轻松显示表的大小,包括索引的大小……可能会让你感到惊讶。
语法:
sp_spaceused 'Tablename'
查看:
http://www.howtogeek.com/howto/database/determine-size-of-a-table-in-sql-server/
以下是一个查询(修改自https://dev59.com/zWsz5IYBdhLWcg3wcnZb#7892349),用于查找以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
CONVERT(DECIMAL,SUM(a.total_pages))
,这样它就可以显示小于GB的表的信息。 - Bogdan Mart你说的尺寸是否指表中记录的数量?如果是这种情况:
SELECT COUNT(*) FROM your_table_name
SELECT pg_size_pretty(pg_relation_size('tablename'));
SELECT table_name, pg_size_pretty(pg_relation_size(table_names.table_name)) AS size from (select table_name from information_schema.tables where table_schema = 'public') AS table_names ORDER BY pg_relation_size(table_names.table_name) DESC;
- Noumenon我知道在SQL 2012中(可能适用于其他版本),您可以执行以下操作:
这将给您列出前1000个表格,并且您可以按数据大小等对其进行排序。
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
如果你只是想找到最大的表,这是一个简单的查询。
-- 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