Display the size of all tables in Sql Server 2005
Posted by Jugal Shah on January 6, 2011
You can use sys.SP_SpaceUsed stored procedure to get the size of all the tables.
Below query will calulate the space of all the tables.
–create temp table to store the result
CREATE TABLE #temptable
(
tablename VARCHAR(100),
numberofrows VARCHAR(100),
reservedsize VARCHAR(50),
datasize VARCHAR(50),
indexsize VARCHAR(50),
unusedsize VARCHAR(50)
)
–create temp table to store the result
CREATE TABLE #temptable
(
tablename VARCHAR(100),
numberofrows VARCHAR(100),
reservedsize VARCHAR(50),
datasize VARCHAR(50),
indexsize VARCHAR(50),
unusedsize VARCHAR(50)
)
–Inserting into temp table
INSERT INTO #temptable
(tablename,
numberofrows,
reservedsize,
datasize,
indexsize,
unusedsize)
EXEC Sp_msforeachtable @command1=“EXEC sp_spaceused ’?'”
SELECT *
FROM #temptable
– drop the temporary table
DROP TABLE #temptable
Size of All the tables
JD said
Nice Post Sir!!