how to check which tables and fields take more space?

Question

I have one database that has a bigger size than expected, how can I query it to retrieve the fields that take more space.

let's say I have 2 only tables

Table1.FieldOne
Table1.FieldTwo
Table2.FieldOne
Table2.FieldTwo

Let's say 99.9% of DB data is in Table2.FieldTwo, how do I query the db?

Of course I have many many tables.

The db is on sqlserver 2000

Answer 1

I can't think of an easy way to do this off the top of my head.

One way would be do dynamically generate the TSQL to list the total size of each column in each table which could be done using something like this:

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL + CHAR(10) + 'UNION ALL' + CHAR(10), '') + 
    'SELECT ''' + QUOTENAME(t.name) + ''' AS TableName, ''' + QUOTENAME(c.name) + ''' AS ColumnName, SUM(DATALENGTH(' + QUOTENAME(c.name) + ')) AS TotalSizeBytes 
    FROM ' + QUOTENAME(t.name)
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'VoterAccount'

SET @SQL = 'SELECT TableName, ColumnName, TotalSizeBytes FROM (' + @SQL + ') x ORDER BY TotalSizeBytes DESC'
PRINT @SQL -- Just PRINT the SQL out. You can then copy it, and execute manually

Which would return e.g.

TableName    ColumnName    TotalSizeBytes
Table1       Column1       123456789
Table1       Column3       34356464
Table2       ColumnA       33987987

However, my concern is this is likely to be a pretty hefty query to run so I'd think carefully about it first. If there are no better suggestions and you try this, it might be worth restricting it to only look at a subset of columns - i.e. ignore any columns that you know from the start are small in size, focus on the columns you know are likely to be the bigger ones.

The ideal solution would be if this information was already held in system tables/accessible via DMVs somehow - unfortunately I don't know if it is, I've never come across it if it is available.