MS SQL 2008 - 获取数据库中的所有表名及其行数 [英] MS SQL 2008 - get all table names and their row counts in a DB

问题描述

我正在尝试通过应用程序(由其他人开发)确定哪些表受到数据更新的影响.数据库中有 200 多个表，我想避免检查每个表的更改.

I'm trying to identify which tables are affected by a data update though an application (developed by others). There are more than 200 tables in the DB and I'd like to avoid checking each of them for changes.

select table_name from information_schema.tables

列出我的数据库中的所有表购买我如何包括行数?

List out all the tables in my DB buy how do I include number of rows as well?

推荐答案

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

看到这个:

• SQL SERVER – 在表中查找行数 – 在数据库中查找最大的表.

这篇关于MS SQL 2008 - 获取数据库中的所有表名及其行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！