Query to list number of records in each table in a

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

Answer 1

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

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. Then, right click a table and select property from the pop up menu;
3. start the trace again;
4. 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], --> 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.

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

Answer 2

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

Output:

Answer 3

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)

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

Answer 5

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

Answer 6

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