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
