Today one of my friend sreejith (colleague) came and ask me, how to find the total numbers of rows of all tables in a given database.The solution is
SELECT ‘[‘ + Schema_name(t.schema_id) + ‘].[‘ + t.name + ‘]’ AS fulltable_name,
Schema_name(t.schema_id) AS schema_name,
t.name AS table_name,
i.ROWS
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i
ON t.object_id = i.id
AND i.indid < 2