Finding Total Number of Rows, Columns in a Database and Tables Sizes

Here is the Script:

USE Northwind 
GO
CREATE TABLE #temp
(
table_name SYSNAME
, row_count INT
, reserved_size VARCHAR(50)
, data_size VARCHAR(50)
, index_size VARCHAR(50)
, unused_size VARCHAR(50)
)

SET NOCOUNT ON

INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name
, a.row_count
, COUNT(*) AS col_count
, a.data_size
FROM #temp a
INNER JOIN information_schema.columns b ON a.table_name COLLATE database_default = b.table_name COLLATE database_default
GROUP BY a.table_name
, a.row_count
, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS INTEGER) DESC

DROP TABLE #temp

Output:

 

Sandeep

a dev, an amateur photographer and a father

 

One thought on “Finding Total Number of Rows, Columns in a Database and Tables Sizes

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: