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.

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

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)

, ,
Previous Post
SQL SERVER – Index Levels and Delete Operations – Page Level Observation
Next Post
SQL SERVER – Quickly Upgrade Your SQL Server

Related Posts

30 Comments. Leave new

Leave a Reply

Menu