How to find the size of a column in the database?

Question

Is there any command for sql server that I can run and see the size of columns in a table? I need to know which column is leaving a heavy table

I've already tried to run some commands, but the only one I found was to see the total size of the table, and I need the size of the column?

Answer 1

Use COL_LENGTH() to Get a Column's Length in SQL Server

Answer 2

WITH 
T AS (SELECT COL_LENGTH('sys.objects', 'name') AS COLUM_LENGTH_BYTE)
SELECT COLUM_LENGTH_BYTE,
       SUM(LEN(name)) AS VOLUME_CHARS, SUM(DATALENGTH(name)) AS VOLUME_BYTES
FROM   sys.objects CROSS JOIN T
GROUP  BY COLUM_LENGTH_BYTE

Will give you :

• the length in byte of the column
• the chars volume amount for all table values
• the byte volume amount for all table values

In the query I use table "sys.objects" and column "name"

Answer 3

Here are two queries on the same table and the results.

USE master; -- version 2019

WITH T AS (SELECT COL_LENGTH('sys.objects', 'name') AS COLUM_LENGTH_BYTE)
SELECT COLUM_LENGTH_BYTE,
       SUM(LEN(name)) AS VOLUME_CHARS, SUM(DATALENGTH(name)) AS VOLUME_BYTES
FROM   sys.objects CROSS JOIN T
GROUP  BY COLUM_LENGTH_BYTE;

COLUM_LENGTH_BYTE VOLUME_CHARS VOLUME_BYTES
----------------- ------------ ------------
256               2268         4536

WITH T AS (SELECT COL_LENGTH('sys.objects', 'type_desc') AS COLUM_LENGTH_BYTE)
SELECT COLUM_LENGTH_BYTE,
       SUM(LEN(type_desc)) AS VOLUME_CHARS, SUM(DATALENGTH(type_desc)) AS VOLUME_BYTES
FROM   sys.objects CROSS JOIN T
GROUP  BY COLUM_LENGTH_BYTE;

COLUM_LENGTH_BYTE VOLUME_CHARS VOLUME_BYTES
----------------- ------------ ------------
120               1717         3434

In table result:

• "COLUM_LENGTH_BYTE" means exactly the length of the column in bytes...
• "VOLUME_CHARS" means exactly the total volume of chars stored in the column...
• "VOLUME_BYTES" means exactly the total volume of bytes stored in the column...

This is based on that the question asks (point 1 - I quote) "size of columns" which can tell the length of the type of columns, and that it continues on to ask (point 2 - I quote) "need to know which column is leaving a heavy table" which seems to indicate that it is trying to find out the volume of data from a column. Therefore:

1. the size of the type, which answers point 1
2. the volume of a column which answers point 2 with two versions, in terms of number of characters and in terms of bytes