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
Thanks for the comment, will get back to you soon… Jugal Shah