SQL 一次计算所有表的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/428458/
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
Counting rows for all tables at once
提问by Allain Lalonde
I'm using SQL Server 2005 and would like to know how I can get a list of all tables with the number of records in each.
我正在使用 SQL Server 2005，想知道如何获取所有表的列表，其中包含每个表的记录数。
I know I can get a list of tables using the sys.tablesview, but I'm unable to find the count.
我知道我可以使用sys.tables视图获取表列表，但我无法找到计数。
Thank you
谢谢
回答by brian
从这里：http: //web.archive.org/web/20080701045806/http: //sqlserver2000.databases.aspfaq.com: 80/how-do-i-get-a-list-of-sql-server-tables -and-their-row-counts.html
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
回答by Tom Wilson
For what it's worth, the sysindexes system table is deprecated in SQL 2008. The above still works, but here's query that works going forward with SQL 2008 system views.
就其价值而言，sysindexes 系统表在 SQL 2008 中已弃用。以上仍然有效，但这里的查询适用于 SQL 2008 系统视图。
select
schema_name(obj.schema_id) + '.' + obj.name,
row_count
from (
select
object_id,
row_count = sum(row_count)
from sys.dm_db_partition_stats
where index_id < 2 -- heap or clustered index
group by object_id
) Q
join sys.tables obj on obj.object_id = Q.object_id
回答by Kev
I might add that sysindexes.rows is an approximation of the number of rows. I'd run a DBCC UPDATEUSAGEif you need a more accurate value. We had this issue on a DB with tables containing over 47-50 million rows and we thought we'd lost around half a million from each of them.
我可能会补充说 sysindexes.rows 是行数的近似值。DBCC UPDATEUSAGE如果您需要更准确的值，我会运行 a 。我们在一个包含超过 47-5000 万行的表的数据库上遇到了这个问题，我们认为我们已经从每个表中损失了大约 50 万。
回答by Paul Lefebvre
Perhaps something like this:
也许是这样的：
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
回答by joaopintocruz
This is my method:
这是我的方法：
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
order by tablename
drop table #rowcount
works like a charm
奇迹般有效
回答by Deepanshu Kalra
These all ways will give you the row count in the result set of a select query. SSMS also gives you an option to see the object details via: "Object Explorer Details" pane. This is available for 2005 and all versions after that
这些所有方法都将为您提供选择查询结果集中的行数。SSMS 还为您提供了一个选项，可以通过以下方式查看对象详细信息：“对象资源管理器详细信息”窗格。这适用于 2005 年及之后的所有版本
Object Explorer Details is a component of SQL Server Management Studio. It provides a tabular view of all the objects in the server and presents a user interface to manage them.
对象资源管理器详细信息是 SQL Server Management Studio 的一个组件。它提供了服务器中所有对象的表格视图，并提供了一个用户界面来管理它们。
To reach "Object Details Window": Click Object you want to see details for in "Object Explorer" pane and press F7. Read More: https://technet.microsoft.com/en-us/library/ms191146(v=sql.90).aspx
要访问“对象详细信息窗口”：在“对象资源管理器”窗格中单击要查看详细信息的对象，然后按 F7。阅读更多：https: //technet.microsoft.com/en-us/library/ms191146(v=sql.90).aspx
Pros:
优点：
- Much faster then the select query as SQL Server stores this information
- Don't need to write/copy-paste the select query everytime - you are just one F7 away
- Gives you much more information and not just about row count
- 比 SQL Server 存储此信息时选择查询要快得多
- 不需要每次都编写/复制粘贴选择查询 - 你只需要一个 F7
- 为您提供更多信息，而不仅仅是行数
Note: You might not have Row Count coming in the Object Details pane by default, you can click on the column header and bring it in.
注意：默认情况下，对象详细信息窗格中可能没有行计数，您可以单击列标题并将其引入。
