sqlserver数据库表大小查询语句

你可以使用以下SQL语句来查询SQL Server数据库中每个表的大小信息：

SELECT 
    t.NAME AS TableName,
    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
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, p.Rows
ORDER BY 
    TotalSpaceKB DESC

这将为您提供每个表的总空间，已用空间和未使用空间的大小。

相关问题

查询sql server某个数据库的大小

查询 SQL Server 某个数据库的大小可以使用以下 SQL 语句：

USE database_name;
SELECT 
    CONVERT(DECIMAL(10,2), SUM(size)/128.0) AS [Total Size in MB],
    CONVERT(DECIMAL(10,2), SUM(FILEPROPERTY(name, 'SpaceUsed'))/128.0) AS [Used Space in MB],
    CONVERT(DECIMAL(10,2), (SUM(size)-SUM(FILEPROPERTY(name, 'SpaceUsed')))/128.0) AS [Free Space in MB]
FROM 
    sys.database_files;

其中，`database_name` 是要查询的数据库名称，可以将其替换为实际的数据库名称。这条 SQL 语句会返回三个列，分别是数据库的总大小、已使用空间和空闲空间，单位均为 MB。

需要注意的是，这里的总大小是指数据库文件的总大小，包括数据文件和日志文件。如果只需要查询数据文件的大小，可以修改查询语句，只查询数据文件的大小，如下所示：

USE database_name;
SELECT 
    CONVERT(DECIMAL(10,2), SUM(size)/128.0) AS [Total Size in MB],
    CONVERT(DECIMAL(10,2), SUM(FILEPROPERTY(name, 'SpaceUsed'))/128.0) AS [Used Space in MB],
    CONVERT(DECIMAL(10,2), (SUM(size)-SUM(FILEPROPERTY(name, 'SpaceUsed')))/128.0) AS [Free Space in MB]
FROM 
    sys.database_files
WHERE 
    type_desc = 'ROWS';

这样只会查询数据文件的大小，不包括日志文件的大小。

查询 SQL Server 某个数据库下所有表的大小

可以使用以下 SQL 查询语句查询 SQL Server 某个数据库下所有表的大小：

USE YourDatabaseName;

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 
    TotalSpaceMB DESC, UsedSpaceMB DESC;

你只需要将 "YourDatabaseName" 替换为你要查询的数据库名称即可。