0

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?

João Víctor
  • 45
  • 5
  • 1
    What do you mean by the "size of the column"? You could get the number of rows in the table (using the `sys` objects) and then multiply it by the size of its data type (for example 4 for an `int`), which would get you, roughly, the size of the data in that column. That, of course, doesn't take into account compression or anything. – Thom A Jun 22 '23 at 11:11
  • 2
    A column can have a fixed or variable length, but what you're most likely interested in is the total amount of data contained in all rows for that column specifically, which is independent of its length (if variable). There are no fast ways of getting that, but there are obvious slow ways (`SUM(CONVERT(BIGINT, DATALENGTH())) FROM Table`). This can be sped up a bit by sampling rows (i.e. `TABLESAMPLE`) and assuming the distribution of data across columns is roughly the same for all rows. – Jeroen Mostert Jun 22 '23 at 11:13

3 Answers3

0
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"

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • Because your code is completely wrong here. It is getting the length of the name, not the length of the data in the rows which is what the OP is looking for. – Sean Lange Jun 28 '23 at 15:44
  • @Sean_Lange says "your code is completely wrong here. It is getting the length of the name" This is completly false and your are lying. This code gives exacgtly what I was saying with the exemple of table "sys.objects" and the column "name". So I don't undertsand why you fire me unjustifiably and probably out of ignorance – SQLpro Jun 30 '23 at 13:00
  • I am certainly not lying nor is my comment out of ignorance. You are getting the sum of the length of the name of the object, not the contents in the table. I was merely one of the downvotes. Perhaps you should try your code on a table in your system and you will see what I mean. – Sean Lange Jun 30 '23 at 13:06
  • 1
    TBH - I did think the same as @SeanLange too. I see now that you are just using the "name" column in `sys.objects` as a generic example of column "foo" in table "bar". I think it would have been clearer to steer away from these system views and just create a dummy table as at first glance it looks like you are trying to provide an answer from metadata - not just with an example that happens to use metadata views – Martin Smith Jun 30 '23 at 13:11
  • 1
    I was right there with you @MartinSmith. The name being passed in the example sure through me off as well. If it was more obvious that you mean the table in question this would be a lot clearer. – Sean Lange Jun 30 '23 at 13:17
  • In other words, you didn't try this query, you read it as fast as you could and said it was stupidity. Nice example of professional attitude ! – SQLpro Jun 30 '23 at 13:29
  • That's why I made a second post clearer to clarify the thing. but what I have noticed for some time in stackoverflow is that, on the one hand, some users rate negatively without saying why and that others make no effort to understand and immediately judge wrongly. I thought that a pro-professional attitude that some claim is to take a step back rather than act in haste! – SQLpro Jun 30 '23 at 13:38
-1

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
Thom A
  • 88,727
  • 11
  • 45
  • 75
SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • Please edit your answer here and remove the personal attack on me. This site should maintain a professional attitude and there is no need for those comments. – Sean Lange Jun 30 '23 at 13:18
  • I never made it personal and admitted my misunderstanding of your original answer. – Sean Lange Jun 30 '23 at 13:21
  • I will remove the point of your lyes when my original answer will be recognized as a true answer. But actually I still see negative note... – SQLpro Jun 30 '23 at 13:23
  • Not my downvote. Perhaps they will share the reasoning. – Sean Lange Jun 30 '23 at 13:48
-3

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

Karan Pawar
  • 1
  • 2