获取所有表名和它们的行数在DB中

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.

我正在尝试识别哪些表受数据更新的影响，尽管应用程序(由其他人开发)。DB中有超过200个表，我希望避免检查每个表的更改。

Is there any other way where I can list all table names along with their row count?

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?

列出我的DB buy中的所有表，如何包含行数?

4 个解决方案

#1

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

See this:

看到这个:

SQL SERVER – Find Row Count in Table – Find Largest Table in Database.
SQL SERVER -查找表中的行数-查找数据库中的最大表。

#2

to get all tables in a database:

获取数据库中的所有表:

select * from INFORMATION_SCHEMA.TABLES

to get all columns in a database:

获取数据库中的所有列:

select * from INFORMATION_SCHEMA.columns

to get all views in a db:

在db中获取所有视图:

select * from INFORMATION_SCHEMA.TABLES where table_type = 'view'

#3

Posted for completeness.

出于完整性的考虑。

If you are looking for row count of all tables in all databases (which was what I was looking for) then I found this combination of this and this to work. No idea whether it is optimal or not:

如果您正在查找所有数据库中的所有表的行数(这正是我要查找的)，那么我发现这个和这个的组合是有效的。不知道它是否是最佳的:

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname, RowsCount int )
DECLARE
     @SQL nvarchar(4000)
SET @SQL='SELECT ''?'' AS DbName, s.name AS SchemaName, t.name AS TableName, p.rows AS RowsCount FROM [?].sys.tables t INNER JOIN sys.schemas s ON t.schema_id=s.schema_id INNER JOIN [?].sys.partitions p ON p.OBJECT_ID = t.OBJECT_ID'

INSERT INTO @AllTables (DbName, SchemaName, TableName, RowsCount)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT DbName, SchemaName, TableName, SUM(RowsCount), MIN(RowsCount), SUM(1)
FROM @AllTables
WHERE RowsCount > 0
GROUP BY DbName, SchemaName, TableName
ORDER BY DbName, SchemaName, TableName

#4

Try this it's simple and fast

试试这个简单快速

SELECT T.name AS [TABLE NAME], I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
   INNER JOIN sys.sysindexes AS I ON T.object_id = I.id 
   AND I.indid < 2 ORDER  BY I.rows DESC

#1

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