Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a
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
query on the rows in a table to get the row count.
Following are the different ways to get the number of records in a table:
List all tables of the database with row counts.
Table_Name = object_name(object_id),
Total_Rows = SUM(st.row_count)
SUM(st.row_count) <> 0
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
List table with row count.
object_name(object_id) = 'app3_employee'
List rows with the row count.
SELECT PKEY, FIRSTNAME, LASTNAME, ROW_NUMBER() OVER (ORDER BY PKEY) AS Position,
COUNT(*) OVER () AS TotalRows