SQL – Find number Rows, Columns for each table in the database

From below query you will get the number of data present in each table of databases. Most consume table data.

–created the temp table to store the tables data size
CREATE TABLE #DBEachTable (
TableName sysname ,
RowCounts INT,
ReservedSize VARCHAR(50),
DataSize VARCHAR(50),
IndexSize VARCHAR(50),
UnusedSize VARCHAR(50))

–insert the tables data in created temp table
INSERT #DBEachTable
EXEC sp_msforeachtable ‘sp_spaceused ”?”’

–fetching the each individual tables data size
SELECT a.TableName,
a.RowCounts,
COUNT(*) AS ColumnCount,
a.DataSize
FROM #DBEachTable a
INNER JOIN information_schema.columns b
ON a.TableName collate database_default
= b.table_name collate database_default
GROUP BY a.TableName, a.RowCounts, a.DataSize
ORDER BY CAST(REPLACE(a.DataSize, ‘ KB’, ”) AS integer) DESC