Below is the query to get the list of tables with sizes in a database:
CREATE PROCEDURE getAllTablesSize
AS
BEGIN
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;
CREATE TABLE
#temp (
[name] varchar(250),
[rows] varchar(50),
[reserved] varchar(50),
[data] varchar(50),
[index_size] varchar(50),
[unused] varchar(50)
);
INSERT #temp EXEC ('sp_msforeachtable ''sp_spaceused ''''?''''''');
UPDATE #temp
SET
[rows] = LTRIM(RTRIM(REPLACE(t.rows,'KB',''))),
[reserved] = LTRIM(RTRIM(REPLACE(t.reserved,'KB',''))),
[data] = LTRIM(RTRIM(REPLACE(t.data,'KB',''))),
[index_size] = LTRIM(RTRIM(REPLACE(t.index_size,'KB',''))),
[unused] = LTRIM(RTRIM(REPLACE(t.unused,'KB','')))
FROM #temp AS t
SELECT
SUM(CAST([reserved] as decimal))/1024 AS 'Total reserved MB',
SUM(CAST([data] as decimal))/1024 AS 'Total data MB',
SUM(CAST([index_size] as decimal))/1024 AS 'Total index_size MB',
SUM(CAST([unused] as decimal))/1024 AS 'Total unused MB'
FROM
#temp
SELECT
[name] ,
CAST([rows] as INT)'rows' ,CAST([reserved] as INT)/1024 'reserved MB',
CAST([data] as INT)/1024 'data MB' ,
CAST([index_size]/1024 as INT)'index_size MB',
CAST([unused] as INT)/1024 'unused MB'
FROM
#temp
ORDER BY name
DROP TABLE #temp;
END
GO
--EXECUTE getAllTablesSize
