Space used in all tables of a Database

Posted: January 7, 2011 by Virendra Yaduvanshi in SQL General
Tags: , , , ,

USE VirendraTest
GO

CREATE TABLE #Virendratemp (
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 #Virendratemp
EXEC sp_msforeachtable ‘sp_spaceused ”?”’

SELECT a.Table_Name,
a.Row_count,
COUNT(*) AS Col_count,
a.Data_size
FROM #Virendratemp 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 #Virendratemp

Advertisements
Comments
  1. Manmohan says:

    Good Notes. Definetly it will Help me for my Project.

    Thank you

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s