查询在数据库中的每个表中列出记录的数量。

How to list row count of each table in the database. Some equivalent of

如何列出数据库中每个表的行数。一些相当于

select count(*) from table1
select count(*) from table2
...
select count(*) from tableN

I will post a solution but other approaches are welcome

我将发布一个解决方案，但其他方法是受欢迎的。

15 个解决方案

#1

If you're using SQL Server 2005 and up, you can also use this:

如果您正在使用SQL Server 2005和up，您还可以使用以下内容:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 

In my opinion, it's easier to handle than the sp_msforeachtable output.

在我看来，它比sp_msforeachtable输出更容易处理。

#2

A snippet I found at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021 that helped me:

我在http://www.sqlteam.com/forums/topic.asp?帮助我TOPIC_ID = 21021:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
order by TableName;

#3

To get that information in SQL Management Studio, right click on the database, then select Reports --> Standard Reports --> Disk Usage by Table.

要获得SQL管理Studio中的信息，右键单击数据库，然后选择报告——>标准报告——表的>磁盘使用情况。

#4

SELECT 
    T.NAME AS 'TABLE NAME',
    P.[ROWS] AS 'NO OF ROWS'
FROM SYS.TABLES T 
INNER JOIN  SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID;

#5

As seen here, this will return correct counts, where methods using the meta data tables will only return estimates.

如图所示，这将返回正确的计数，使用元数据表的方法只返回估计值。

    CREATE PROCEDURE ListTableRowCounts 
    AS 
    BEGIN 
        SET NOCOUNT ON 

        CREATE TABLE #TableCounts
        ( 
            TableName VARCHAR(500), 
            CountOf INT 
        ) 

        INSERT #TableCounts
            EXEC sp_msForEachTable 
                'SELECT PARSENAME(''?'', 1), 
                COUNT(*) FROM ? WITH (NOLOCK)' 

        SELECT TableName , CountOf 
            FROM #TableCounts
            ORDER BY TableName 

        DROP TABLE #TableCounts
    END
    GO

#6

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'

Output:

输出:

#7

The first thing that came to mind was to use sp_msForEachTable

首先想到的是使用sp_msForEachTable。

exec sp_msforeachtable 'select count(*) from ?'

that does not list the table names though, so it can be extended to

但是，这并没有列出表名，因此可以扩展到。

exec sp_msforeachtable 'select parsename(''?'', 1),  count(*) from ?'

The problem here is that if the database has more than 100 tables you will get the following error message:

这里的问题是，如果数据库有超过100个表，您将得到以下错误消息:

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

查询已经超过了可以在结果网格中显示的结果集的最大数量。只有前100个结果集显示在网格中。

So I ended up using table variable to store the results

因此，我使用了table变量来存储结果。

declare @stats table (n sysname, c int)
insert into @stats
    exec sp_msforeachtable 'select parsename(''?'', 1),  count(*) from ?'
select 
    * 
from @stats
order by c desc

#8

Well luckily SQL Server management studio gives you a hint on how to do this. Do this,

幸运的是，SQL Server management studio给了您一个关于如何操作的提示。这样做,

1. start a SQL Server trace and open the activity you are doing (filter by your login ID if you're not alone and set the application Name to Microsoft SQL Server Management Studio), pause the trace and discard any results you have recorded till now;
2. 启动一个SQL Server跟踪并打开您正在做的活动(如果您不是一个人，并将应用程序名称设置为Microsoft SQL Server Management Studio)，请暂停跟踪并删除您现在所记录的任何结果;
3. Then, right click a table and select property from the pop up menu;
4. 然后，右键单击一个表，从弹出菜单中选择属性;
5. start the trace again;
6. 重新开始跟踪;
7. Now in SQL Server Management studio select the storage property item on the left;
8. 现在在SQL Server Management studio中选择左侧的存储属性项;

Pause the trace and have a look at what TSQL is generated by microsoft.

暂停跟踪，看看微软生成了什么TSQL。

In the probably last query you will see a statement starting with exec sp_executesql N'SELECT

在可能的最后一个查询中，您将看到从exec sp_executesql N'SELECT开始的语句。

when you copy the executed code to visual studio you will notice that this code generates all the data the engineers at microsoft used to populate the property window.

当您将已执行的代码复制到visual studio时，您会注意到，这段代码生成了microsoft的工程师用来填充属性窗口的所有数据。

when you make moderate modifications to that query you will get to something like this:

当你对那个查询做适当的修改时，你会得到这样的结果:

SELECT
SCHEMA_NAME(tbl.schema_id)+'.'+tbl.name as [table], --> something I added
p.partition_number AS [PartitionNumber],
prv.value AS [RightBoundaryValue],
 fg.name AS [FileGroupName],
CAST(pf.boundary_value_on_right AS int) AS [RangeType],
CAST(p.rows AS float) AS [RowCount],
p.data_compression AS [DataCompression]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = idx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON  pf.function_id = prv.function_id

Now the query is not perfect and you could update it to meet other questions you might have, the point is, you can use the knowledge of microsoft to get to most of the questions you have by executing the data you're interested in and trace the TSQL generated using profiler.

现在查询并不完美,你可以更新你可能会遇到其他问题,关键是,你可以使用微软的知识被执行的大多数问题你有您感兴趣的数据和跟踪TSQL使用分析器生成。

I kind of like to think that MS engineers know how SQL server work and, it will generate TSQL that works on all items you can work with using the version on SSMS you are using so it's quite good on a large variety releases prerviouse, current and future.

我有点想，工程师们知道SQL server是如何工作的，它会生成TSQL，在所有的项目上都能工作，你可以使用你正在使用的SSMS版本，所以它在很大的种类上很好，可以发布前，现在和将来。

And remember, don't just copy, try to understand it as well else you might end up with the wrong solution.

记住，不要只是复制，试着去理解它，否则你可能会得到错误的解决方案。

Walter

沃尔特

#9

The accepted answer didn't work for me on Azure SQL, here's one that did, it's super fast and did exactly what I wanted:

这个公认的答案对我来说并不适用于Azure SQL，这里有一个，它非常快，并且完全符合我的要求:

select t.name, s.row_count
from sys.tables t
join sys.dm_db_partition_stats s
  ON t.object_id = s.object_id
    and t.type_desc = 'USER_TABLE'
    and t.name not like '%dss%'
    and s.index_id = 1
order by s.row_count desc

#10

I think that the shortest, fastest and simplest way would be:

我认为最短、最快和最简单的方法是:

SELECT
    object_name(object_id) AS [Table],
    SUM(row_count) AS [Count]
FROM
    sys.dm_db_partition_stats
WHERE
    --object_schema_name(object_id) = 'dbo' AND 
    index_id < 2
GROUP BY
    object_id

#11

You could try this:

你可以试试这个:

SELECT  OBJECT_SCHEMA_NAME(ps.object_Id) AS [schemaname],
        OBJECT_NAME(ps.object_id) AS [tablename],
        row_count AS [rows]
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_Id) <> 'sys' AND ps.index_id < 2
ORDER BY 
        OBJECT_SCHEMA_NAME(ps.object_Id),
        OBJECT_NAME(ps.object_id)

#12

This approaches uses string concatenation to produce a statement with all tables and their counts dynamically, like the example(s) given in the original question:

这种方法使用字符串连接来生成一个包含所有表的语句，它们的计数是动态的，就像在最初的问题中给出的示例:

          SELECT COUNT(*) AS Count,'[dbo].[tbl1]' AS TableName FROM [dbo].[tbl1]
UNION ALL SELECT COUNT(*) AS Count,'[dbo].[tbl2]' AS TableName FROM [dbo].[tbl2]
UNION ALL SELECT...

Finally this is executed with EXEC:

最后，执行EXEC:

DECLARE @cmd VARCHAR(MAX)=STUFF(
                    (
                        SELECT 'UNION ALL SELECT COUNT(*) AS Count,''' 
                              + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) 
                              + ''' AS TableName FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
                        FROM INFORMATION_SCHEMA.TABLES AS t
                        WHERE TABLE_TYPE='BASE TABLE'
                        FOR XML PATH('')
                    ),1,10,'');
EXEC(@cmd);

#13

Fastest way to find row count of all tables in SQL Refreence (http://www.codeproject.com/Tips/811017/Fastest-way-to-find-row-count-of-all-tables-in-SQL)

在SQL刷新中找到所有表的行计数的最快方法(http://www.codeproject.com/tips/811017 /最快到find-row-count- all-tab -in-SQL)

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

#14

If you use MySQL >4.x you can use this:

如果你用的是MySQL >4。你可以用这个:

select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";

Keep in mind that for some storage engines, TABLE_ROWS is an approximation.

请记住，对于某些存储引擎，TABLE_ROWS是一个近似。

#15

select T.object_id, T.name, I.indid, I.rows 
  from Sys.tables T 
  left join Sys.sysindexes I 
    on (I.id = T.object_id and (indid =1 or indid =0 ))
 where T.type='U'

Here indid=1 means a CLUSTERED index and indid=0 is a HEAP

这里indid=1表示聚集索引，indid=0是堆。