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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: