SQL Server Determine Physical Size of Table Columns

Question

Is it possible to find out how big the data is in KB or MB for individual columns in a table? I have a script which tells me the physical size of each table, but I would like to know how much of that is taken up by certain columns in the database, especially when I have XML stored in a column.

Any help much appreciated

Cheers

Answer 1

You should be able to use the datalength function, something like

select sum(datalength(yourfield))
from yourtable

This will summate the datalengths of all the entries of that field in the table - it will not account for overheads such as variable length field pointers, space in the nullability bitmap etc.

Answer 2

For view size of "all columns a table", you can first generate fields of your table with:

SELECT 'SUM(DATALENGTH('+Column_name+')) / (1024*1024) as '+Column_name+'_MB,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '--TABLE_NAME--'

and next, run this query:

SELECT
    /*result previous query (with remove last comma)*/
FROM --TABLE_NAME--

Answer 3

This will return all columns in a specific database with their data size and can easily be updated to only return the numbers for a specific table or column.

USE [YourDatabase]
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #temp (tablename varchar(max), columnname varchar(max), sizeinkb float)

DECLARE MY_CURSOR Cursor LOCAL FAST_FORWARD 
FOR SELECT table_name, column_name, table_schema FROM INFORMATION_SCHEMA.COLUMNS

Open My_Cursor 
DECLARE @table varchar(max), @column varchar(max), @schema varchar(max)
Fetch NEXT FROM MY_Cursor INTO @table, @column, @schema
While (@@FETCH_STATUS <> -1)
BEGIN
    DECLARE @sql varchar(1000) = 'INSERT #temp SELECT ''' + @schema + '.' + @table + ''', ''' + @column + ''', sum(isnull(datalength([' + @column + ']), 0)) / 1024.0 FROM [' + @schema + '].[' + @table + '] (NOLOCK)'
    EXEC (@sql)

    FETCH NEXT FROM MY_CURSOR INTO @table, @column, @schema
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

SELECT *, sizeinkb / 1024.0 sizeinmb FROM #temp ORDER BY 3 DESC

Answer 4

Select SUM(DATALENGTH(columnsA)) / 1024.0 AS KB