Get All Details from Database and tables with rows and Db size

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0)

--Check 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 data from table

SELECT * FROM  # Temptable

-- # Count tatal rows

SELECT SUM(CAST([rows] AS int)) AS [rows]FROM   # Temptable

-- # Drop Temp Table

DROP TABLE # Temptable