Problem

Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.

Possible Solutions

Following are the different ways to get the number of records in a table:

First

List all tables of the database with row counts.

SELECT
Table_Name = object_name(object_id),
Total_Rows = SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
GROUP BY
object_name(object_id)
HAVING
SUM(st.row_count) <> 0
ORDER BY
object_name(object_id)

Second

List table with the number of rows in table.

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U' and sysobjects.[name]='App3_Employee'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC
GO 

Third

List table with row count.

SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'app3_employee' 

Fourth

List rows with the row count.

SELECT PKEY, FIRSTNAME, LASTNAME, ROW_NUMBER() OVER (ORDER BY PKEY) AS Position, 
COUNT(*) OVER () AS TotalRows
FROM app_employee