1st Method:- SELECT OBJECT_NAME(ID) As TableName, Rows As TotalRows
FROM SysIndexes WHERE IndID IN (0,1)
AND OBJECTPROPERTY(ID, 'IsUserTable') = 1
ORDER BY TableName;
2nd Method:- SELECT OBJECT_NAME(Object_id) As TableName, SUM(Rows) As TotalRows
FROM sys.partitions
WHERE OBJECTPROPERTY(Object_id, 'IsUserTable') = 1 AND Index_ID IN (0,1)
GROUP BY OBJECT_NAME(Object_id)
ORDER BY TableName;