MS SQL Determining Table sizes (in KB)

Today when I was looking at my database file size and to my surprise I saw the size of database was more than 10 Gigabytes. Then I was keen in knowing tables, which are contributing to this much big size. And Saravana, my colleague gave a fantastic script to find out space used by each table. I thought let’s share it with everybody. Thanks to Microsoft who gave a built in stored procedure sp_spaceused.
With the help of this stored procedure one can easily find out the space with one line code
exec sp_spaceused <Table Name >
sample output

name rows reserved data index_size unused 17991232 12636448 KB 11051664 KB 1583816 KB 968 KB
But I just wanted to find out tables, which are contributing more. Here is that handy script for any DBA

DECLARE
@DBTABLES
AS TABLE
(
SNO INT IDENTITY(1,1),
TABLENAME
VARCHAR(256)
)
INSERT INTO @DBTABLES
SELECT
table_schema + '.' + table_name
FROM
information_schema.TABLES
WHERE
table_type = 'BASE TABLE'
DECLARE
@DBTABLESINFO
AS TABLE
(
SNO INT IDENTITY(1,1),
TABLENAME VARCHAr(256),
ROWS char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)

DECLARE @COUNT
INT , @CURRENT INT, @TABLENAME VARCHAR(256)
SELECT @COUNT = COUNT(*) FROM @DBTABLES
SET @CURRENT = 1
WHILE (@COUNT >= @CURRENT)
BEGIN
SELECT @TABLENAME =
TABLENAME FROM @DBTABLES WHERE SNO =
@CURRENT
INSERT INTO
@DBTABLESINFO

EXEC sp_spaceused @TABLENAME
SET @CURRENT =
@CURRENT + 1
END

SELECT
TABLENAME,
ROWS,
DATA,
convert(bigint, left(DATA,len(data)-3) )
FROM
@DBTABLESINFO

ORDER BY 4 DESC

Hope this will help.
Regards
Bharat Mane