博客

要查询SQL Server数据库中所有表及其记录行数，您可以按照以下步骤进行操作：

1. 打开SQL Server Management Studio（SSMS）。

2. 在“对象资源管理器”中选择要查询的数据库。

3. 在“查询编辑器”中输入以下SQL查询句：

SELECT 
    t.name AS TableName,
    SUM(p.rows) AS RowCounts
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
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.name
ORDER BY 
    SUM(p.rows) DESC

1. 单击“执行”按钮或按F5键运行查询。

2. 查询结果将显示所有表的名称和记录行数，按记录行数从高到低排序。

以下是两个使用SQL Server查询所有表及其记录行数的示例：

示例1：查询所有表及其记录行数

SELECT 
    t.name AS TableName,
    SUM(p.rows) AS RowCounts
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
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.name
ORDER BY 
    SUM(p.rows) DESC

示例2：查询指定数据库中所有表及其记录行数

USE [database_name]
GO

SELECT 
    t.name AS TableName,
    SUM(p.rows) AS RowCounts
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
WHERE 
    t.is_ms_shipped = 0
GROUP BY    t.name
ORDER BY 
    SUM(p.rows) DESC

请注意，以上示例中的“database_name”应替换为要查询的数据库的名称。