How to Fetch the Row Count for All Tables in a SQL Server Database

How to fetch the row count for all tables in a SQL SERVER database

The following SQL will get you the row count of all tables in a database:

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

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

Count total rows of all tables in a database SQL Server

You can take a glance to the following article;

Different approaches of counting number of rows in a table

This is my favorite one;

 SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema]
          ,OBJECT_NAME(p.[object_id]) AS [table_name]
          ,SUM(p.[rows]) AS [row_count]
    FROM [sys].[partitions] p
    INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
    WHERE p.[index_id] < 2
    GROUP BY p.[object_id]
        ,t.[schema_id]
    ORDER BY 1, 2 ASC

This one find out total number of the SQL Database

SELECT 
      SUM(p.[rows]) AS [row_count]
FROM [sys].[partitions] p
INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
WHERE p.[index_id] < 2

Get row count of all tables in database: SQL Server

Right click on database -> Reports -> Standard Reports -> Disk usage by Top Tables

Get row count for a list of tables

try using sys.dm_db_partition_stats DMV..

  select 
object_name(object_id) as tablename,sum(row_count) as totalrows
 from sys.dm_db_partition_stats
 where object_name(object_id) like 'Bb%'--gives tables count which start with bb*
 group by object_id 

This may not be accurate enough (very little deviation) when you have lot of inserts ,deletes and check the count immediately..

if you are bent on using sp_msforeach..

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  where table_name like 'BB%' ORDER BY table_name, row_count DESC

References:

How to fetch the row count for all tables in a SQL SERVER database

List of all tables across all databases with row count of each table

There's a quick way to get row counts using SQL Server metadata. You could add this into your query in @SQL:

SELECT [Rows] = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=@YourObjectId   
AND (index_id=0 or index_id=1);

I believe that would make the full @SQL as follows. Untested, but should at least be pretty close:

SELECT @SQL = 'SELECT @@SERVERNAME
        ,''?''
        ,s.name
        ,t.name
        ,SUM(p.row_count) as [rows]
        FROM sys.tables t 
         JOIN sys.schemas s on t.schema_id=s.schema_id 
         LEFT JOIN sys.dm_db_partition_stats p
           ON p.object_id = t.object_id
             and (p.index_id = 0 or p.index_id = 1)
         WHERE @@SERVERNAME LIKE ''%' + ISNULL(@SearchSvr, '') + '%''
         AND ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%''
         AND s.name LIKE ''%' + ISNULL(@SearchS, '') + '%''
         AND t.name LIKE ''%' + ISNULL(@SearchTbl, '') + '%''
         AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'')
        GROUP BY s.name, t.name
           '

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

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

Count the Number of Tables in a SQL Server Database

You can use INFORMATION_SCHEMA.TABLES to retrieve information about your database tables.

As mentioned in the Microsoft Tables Documentation:

INFORMATION_SCHEMA.TABLES returns one row for each table in the current database for which the current user has permissions.

The following query, therefore, will return the number of tables in the specified database:

USE MyDatabase
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

---

As of SQL Server 2008, you can also use sys.tables to count the the number of tables.

From the Microsoft sys.tables Documentation:

sys.tables returns a row for each user table in SQL Server.

The following query will also return the number of table in your database:

SELECT COUNT(*)
FROM sys.tables

List row count of each view and table

You can use dynamic SQL to build a giant chain of union all select statements:

declare @sql nvarchar(max) = ''

-- use an undocumented(?) trick with string concatenation in a select statement
select @sql = @sql + 'union all select ' + 
    '''' + TABLE_NAME + ''' as TABLE_NAME, ' + 
    '''' + TABLE_TYPE + ''' as TABLE_TYPE, ' + 
    '(select count(*) from ' + TABLE_NAME + ') as [COUNT]' + 
    char(13) + char(10) 
from INFORMATION_SCHEMA.TABLES

-- remove leading "union all"
set @sql = right(@sql, len(@sql)-len('union all '))

--print @sql    -- to check what's going to be executed

exec sp_executesql @sql

The SQL it builds and executes looks like:

select 'customers' as TABLE_NAME, 'BASE TABLE' as TABLE_TYPE, (select count(*) from customers) as [rowcount]
union all select 'items' as TABLE_NAME, 'BASE TABLE' as TABLE_TYPE, (select count(*) from items) as [rowcount]
union all select 'orders' as TABLE_NAME, 'VIEW' as TABLE_TYPE, (select count(*) from orders) as [rowcount]
union all ...

Unfortunately you cannot execute dynamic SQL as a column in a select statement - there is nothing that would allow you to do anything as simple as:

select table_name, table_type, exec('select count(*) from ' + table_name) as [count]
into test.dbo.report --         /\== doesn't work
from test.INFORMATION_SCHEMA.tables;

---

Related Topics