It is pretty useful to check space usage of your tables. SQL server gives a nice system stored procedure called sp_spaceused that returns what you want. You can run the following script on your database and export the results to excel.
You can come up with nice graphs showing why your data base is big and what partitioning strategy to choose. For example I ran this query for a project with a 10GB database. I could not understand why only a few million small records occupy 10GB. The result was expectable. There was an exception log table which occupied half of the space and dropping that table saved so much time for everybody.
DECLARE @spu TABLE(NAME VARCHAR(max), [ROWS] BIGINT, reserved VARCHAR(50), DATA VARCHAR(50), index_size VARCHAR(50), unused VARCHAR(50)
)
DECLARE @TName VARCHAR(80)
DECLARE C CURSOR FAST_FORWARD READ_ONLY FOR
SELECT o.name FROM sys.objects AS o
WHERE o.schema_id = SCHEMA_ID('dbo')
AND o.type = 'U'
OPEN C
FETCH NEXT FROM C INTO @Tname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @spu
EXEC sys.sp_spaceused @objname = @TName -- nvarchar(776)
FETCH NEXT FROM C INTO @Tname
END
CLOSE C
DEALLOCATE C
SELECT * FROM @spu ORDER BY DATA DESC
Quality of Data 
December 12, 2013 at 6:30 pm
Your script skips over tables names with dots. For instance, there is a table in my database called ‘Audit.Data’. For such tables, sp_spaceused needs to be called like this: exec sp_spaceused ‘Audit.Data’. But your script only feeds it the name of ‘Data’ (its name in sysobjects), so sp_spaceused fails for those tables.
December 12, 2013 at 10:02 pm
Boadad, the script only looks for the tables in schema ‘dbo’. It should be slightly modified to include other schemas. If the table’s name in sys.objects is Data, then Audit is a schema name and you can use scripts such as select * from Audit.data. However, if the dot was part of the table name, above script would work, and you had to use select * from [Audit.Data] instead.
December 13, 2013 at 9:05 pm
Thanks to your response, now I understand that “Audit” is a different schema for my database. I got your query to work for me by substituting this in:
SELECT s.name + ‘.’ + o.name
FROM sys.objects o
JOIN sys.schemas s on o.schema_id = s.schema_id
Best regards.
March 3, 2014 at 7:16 am
To have more accurate values, it’s necessary to do a “UpdateUsage”
http://technet.microsoft.com/en-us/library/ms188414.aspx
Another more “elegant” way is to use “sp_MSforeachtable” to treat all the tables insteat of a cursor.
— Update stats
DBCC UPDATEUSAGE (0)
go
— Size for each tables
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”
go
March 4, 2014 at 5:26 pm
@Kk.False, Thanks for your contribution to this discussion. I have read that sp_MSforeachtable is undocumented and unsupported. Do you think it will ever go away?
Also, on http://technet.microsoft.com/en-us/library/ms188414.aspx there are some best practices listed for DBCC UPDATEUSAGE, the first of which reads: “Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.”