SQL Script to find total records of all tables.

This T-SQL script will return number of rows for each table in a database and total records across all tables. This will be very much useful for SQL admin to watch the data growth in database.

SELECT Row_Number() over (Order by sysindx.rowcnt DESC ) AS SlNo

,sysObj.name AS ‘Table Name’,sysindx.rowcnt AS ‘Row Count’

FROM sysobjects sysObj

inner join sysindexes sysindx

on sysindx.id = sysObj.id

WHERE sysindx.indid IN(0,1)

AND sysObj.xtype = ‘u’

AND sysObj.name NOT IN(’sysdiagrams’)

COMPUTE SUM(sysindx.rowcnt);



Note: This will work only on MSSQL 2005 and later. Because Compute and Row_Number were introduced new in 2005.