How to find out all table details (table name, total rows in table, table size (in KB)) for any given database.

DECLARE
@DBTABLES
AS TABLE
(
SNO INT IDENTITY(1,1),
TABLENAME
VARCHAR(256)
)
INSERT INTO @DBTABLES
SELECT
table_schema + '.' + table_name
FROM
information_schema.TABLES
WHERE
table_type = 'BASE TABLE'
DECLARE
@DBTABLESINFO
AS TABLE
(
SNO INT IDENTITY(1,1),
TABLENAME VARCHAr(256),
ROWS char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)

DECLARE @COUNT
INT , @CURRENT INT, @TABLENAME VARCHAR(256)
SELECT @COUNT = COUNT(*) FROM @DBTABLES
SET @CURRENT = 1
WHILE (@COUNT >= @CURRENT)
BEGIN
SELECT @TABLENAME =
TABLENAME FROM @DBTABLES WHERE SNO =
@CURRENT
INSERT INTO
@DBTABLESINFO

EXEC sp_spaceused @TABLENAME
SET @CURRENT =
@CURRENT + 1
END


SELECT
TABLENAME,
ROWS,
DATA,
convert(bigint, left(DATA,len(data)-3) )
FROM
@DBTABLESINFO ---where TableName like '%_1'

ORDER BY 4 DESC