SQL Script to list out name of tables and its records in the database

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.

EXEC sp_spaceused

-- Table row counts and sizes.

CREATE TABLE #temptable 

( 

    [name] NVARCHAR(128),

    [rows] CHAR(11),

    reserved VARCHAR(18), 

    data VARCHAR(18), 

    index_size VARCHAR(18),

    unused VARCHAR(18)

) 

 

INSERT  #temptable  EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *

FROM    #temptable  order by CAST([rows] AS int) desc

-- # of rows.

-- SELECT SUM(CAST([rows] AS int)) AS [rows]

-- FROM    #temptable  order by [rows]

DROP TABLE #temptable

Result