SQL SERVER – Find Row Count in Table – Find Largest Table in Database – Part 2

Last Year I wrote article on the subject SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. It is very good to see excellent participation there. In my script I had not taken care of table schema. SQL Server Expert Ameena has modified the same script to include the schema. Here is the new modified script.

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC


Above query will return following resultset.

You can see in resultset that tablename is prefixed with Schema name and numbers of the rows are listed in the table.

If you are using earlier version of SQL Server 2000 you may want to run following command to update the pages and its statistics.

DBCC updateusage(adventureworks)

If you have better query to find out table row count, please share here.

Reference : Pinal Dave (https://blog.sqlauthority.com)