Query to list number of records in each table in a database

Question

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

User · Answer

Shnugo s answer is the ONLY one that works in Azure with Externa Tables    1  Azure SQL doesn t support sp MSforeachtable at all and  2  rows in sys partitions for an External table is always 0

User · Answer

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   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  lt  2  ORDER  BY I rows DESC

User · Answer

Well luckily SQL Server management studio gives you a hint on how to do this   Do this     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  Then  right click a table and select property from the pop up menu  start the trace again  Now in SQL Server Management studio select the storage property item on the left    Pause the trace and have a look at what TSQL is generated by microsoft   In the probably last query you will see a statement starting with 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   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   -- gt  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  lt  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    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   And remember  don t just copy  try to understand it as well else you might end up with the wrong solution   Walter

User · Answer

This sql script gives the schema  table name and row count of each table in a database selected   SELECT SCHEMA NAME schema id  AS  SchemaName    Tables  name AS  TableName   SUM  Partitions   rows   AS  TotalRowCount  FROM sys tables AS  Tables  JOIN sys partitions AS  Partitions  ON  Tables   object id     Partitions   object id  AND  Partitions  index id IN   0  1   -- WHERE  Tables  name   N name of the table  GROUP BY SCHEMA NAME schema id    Tables  name order by  TotalRowCount  desc   Ref  https   blog sqlauthority com 2017 05 24 sql-server-find-row-count-every-table-database-efficiently   Another way of doing this   SELECT  o NAME TABLENAME    i rowcnt  FROM sysindexes AS i   INNER JOIN sysobjects AS o ON i id   o id  WHERE i indid  lt  2  AND OBJECTPROPERTY o id   IsMSShipped     0 ORDER BY i rowcnt desc

User · Answer

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   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

User · Answer

USE DatabaseName CREATE TABLE  counts       table name varchar 255       row count int    EXEC sp MSForEachTable  command1  INSERT  counts  table name  row count  SELECT        COUNT    FROM    SELECT table name  row count FROM  counts ORDER BY table name  row count DESC DROP TABLE  counts

User · Answer

From this question  https   dba stackexchange com questions 114958 list-all-tables-from-all-user-databases 230411 230411  I added record count to the answer provided by  Aaron Bertrand that lists all databases and all tables   DECLARE  src NVARCHAR MAX    sql NVARCHAR MAX    SELECT  sql   N     src   N  UNION ALL  SELECT    d   as   database         s name COLLATE SQL Latin1 General CP1 CI AI as   schema        t name COLLATE SQL Latin1 General CP1 CI AI as   table         ind rows as record count   FROM   d  sys schemas AS s   INNER JOIN   d  sys tables AS t ON s  schema id    t  schema id    INNER JOIN   d  sys sysindexes AS ind ON t  object id    ind  id    where ind indid  lt  2    SELECT  sql    sql   REPLACE  src    d   name    FROM sys databases   WHERE database id  gt  4     AND  state    0     AND HAS DBACCESS name    1   SET  sql   STUFF  sql  1  10  CHAR 13    CHAR 10     PRINT  sql  --EXEC sys sp executesql  sql

User · Answer

sp MSForEachTable  DECLARE  t AS VARCHAR MAX    SELECT  t   CAST COUNT 1  as VARCHAR MAX      CHAR 9    CHAR 9          FROM     PRINT  t    Output

User · Answer

I want to share what s working for me  SELECT       QUOTENAME SCHEMA NAME sOBJ schema id           QUOTENAME sOBJ name  AS  TableName          SUM sdmvPTNS row count  AS  RowCount  FROM       sys objects AS sOBJ       INNER JOIN sys dm db partition stats AS sdmvPTNS             ON sOBJ object id   sdmvPTNS object id WHERE        sOBJ type    U        AND sOBJ is ms shipped   0x0       AND sdmvPTNS index id  lt  2 GROUP BY       sOBJ schema id         sOBJ name ORDER BY  TableName  GO   The database is hosted in Azure and the final result is     Credit  https   www mssqltips com sqlservertip 2537 sql-server-row-count-for-all-tables-in-a-database

User · Answer

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  lt  2 GROUP BY     object id

User · Answer

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   lt  gt   sys  AND ps index id  lt  2 ORDER BY          OBJECT SCHEMA NAME ps object Id           OBJECT NAME ps object id

User · Answer

You can copy  past and execute this piece of code to get all table record counts into a table  Note  Code is commented with instructions  create procedure RowCountsPro as begin --drop the table if exist on each exicution IF OBJECT ID  N dbo RowCounts   N U   IS NOT NULL  DROP TABLE dbo RowCounts  -- creating new table CREATE TABLE RowCounts     TableName             VARCHAR 150     RowCount                INT    Reserved                  NVARCHAR 50     Data                         NVARCHAR 50     Index Size                NVARCHAR 50     UnUsed                    NVARCHAR 50   --inserting all records INSERT INTO RowCounts  TableName    RowCount   Reserved   Data   Index Size   UnUsed   --   sp MSforeachtable  System Procedure   sp spaceused      param to get records and resources used EXEC sp MSforeachtable  sp spaceused       -- selecting data and returning a table of data SELECT  TableName    RowCount   Reserved   Data   Index Size   UnUsed  FROM RowCounts ORDER BY  TableName  end   I have tested this code and it works fine on SQL Server 2014

User · Answer

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

User · Answer

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

User · Answer

If you use MySQL  4 x you can use this   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

User · Answer

The first thing that came to mind was to use 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      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    So I ended up using table variable to store the results  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

User · Answer

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   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

User · Answer

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

User · Answer

A snippet I found at http   www sqlteam com forums topic asp TOPIC ID 21021 that helped me   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

User · Answer

SELECT   Schema name A schema id          A NAME   AS TableName   Sum B rows AS RecordCount      FROM   sys objects A INNER JOIN sys partitions B      ON A object id   B object id WHERE  A type    U           GROUP  BY A schema id A NAME    QUERY PHOTO QUERY RESULT PHOTO

User · Answer

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

User · Answer

If you re using SQL Server 2005 and up  you can also use this   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  gt  255 AND        i index id  lt   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

[sql-server] Query to list number of records in each table in a database

Examples related to sql-server

Examples related to database