sqlserver表空间查看

在SQL Server中，我们可以使用系统视图和函数来查询数据库中表的使用空间信息，以下是一些常用的方法：

1、使用sys.dm_db_partition_stats视图

sys.dm_db_partition_stats视图提供了有关数据库分区统计信息的详细信息，包括每个分区的文件组、页数、可用空间等，以下是一个查询示例：

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 i.OBJECT_ID > 255 排除索引ID为255的聚集索引
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name;

2、使用sp_spaceused存储过程

sp_spaceused存储过程可以返回指定表或索引的空间使用情况，以下是一个查询示例：

EXEC sp_spaceused '表名';

3、使用DBCC SHOWCONTIG命令

DBCC SHOWCONTIG命令可以显示表的碎片信息，以下是一个查询示例：

DBCC SHOWCONTIG('表名');

4、使用fn_get_database_files函数

fn_get_database_files函数可以返回数据库中所有文件的详细信息，包括文件名、文件类型、大小等，以下是一个查询示例：

SELECT
    name AS LogicalName,
    physical_name AS PhysicalFileName,
    size * 8 / 1024 AS FileSizeMB,
    type_desc AS FileTypeDescription,
    is_readonly AS IsReadOnly,
    is_offline AS IsOffline,
    is_percent_free AS IsPercentFree,
    can_seek_all_pages AS CanSeekAllPages,
    can_lock_all_pages AS CanLockAllPages,
    can_modify_all_pages AS CanModifyAllPages,
    can_delete_all_pages AS CanDeleteAllPages,
    can_update_all_pages AS CanUpdateAllPages,
    can_insert INTO AS CanInsertInto,
    can_select INTO AS CanSelectInto,
    can_execute AS CanExecute,
    create_date AS CreateDate,
    modify_date AS ModifyDate,
    growth AS Growth,
    is_user_accessible AS IsUserAccessible,
    is_declare @fileId INT = DB_ID('数据库名'); 设置数据库ID，DB_ID('TestDB')
DECLARE @fileInfo TABLE (fileId INT PRIMARY KEY, logicalName NVARCHAR(128), physicalName NVARCHAR(262), size MONEY, type DECIMAL(10, 2), fileGroupId INT); 创建临时表用于存储文件信息
INSERT INTO @fileInfo (fileId, logicalName, physicalName, size, type) 插入文件信息到临时表
EXEC sp_msforeachdb @command1='USE [?]; DBCC INPUTBUFFER(1); DBCC FLUSHPROCINDB();', @replacechar1=N'?', @iterations=1; 遍历所有数据库，获取文件信息
SELECT * FROM @fileInfo; 查询临时表中的文件信息；

问题与解答：

Q1：如何查询数据库中表的行数？

A1：可以使用sys.dm_db_partition_stats视图或者sp_spaceused存储过程来查询表的行数。SELECT p.Rows FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.object_id WHERE t.name = '表名';。

Q2：如何查询数据库中表的总空间、已用空间和未用空间？

A2：可以使用sys.dm_db_partition_stats视图或者sp_spaceused存储过程来查询表的总空间、已用空间和未用空间。SELECT 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;。