How to get the total character size of an SQL column?

Question

I want to take the total size of characters that a column supports, example INT supports 10 characters.

I tried to use DATALENGTH that does not return the number of characters, but the amount of bytes that the column occupies.

SELECT DATALENGTH(CAMPO) FROM TABELA

Answer 1

Make a query in the following tables:

• sys.types
• sys.all_columns

Follow the consultation

SELECT cNmColuna = C.name
    ,cTpColuna = UPPER(TYPE_NAME(C.user_type_id))
    ,iMaxDigit = CASE
                    WHEN T.precision = 0
                    THEN C.max_length
                    ELSE T.precision
                 END
FROM sys.all_columns C WITH(NOLOCK)
    INNER JOIN sys.types T WITH(NOLOCK) ON T.user_type_id = C.user_type_id
    WHERE C.object_id = Object_Id('Nome da Tabela')

Answer 2

According to Article:

http://technet.microsoft.com/en-us/library/ms187745.aspx

int supports 10 characters:

Máximo: 2.147.483.647
Mínimo: -2.147.483.648

Dynamically speaking, SQL Server can only count characters from character fields (varchar, nvarchar, text, etc.). Of numeric fields, it is worth following the table of intervals of the link.

Answer 3

IF ((SELECT character_maximum_length FROM information_schema.columns WHERE (table_name = 'SUC_CONFIG') AND (COLUMN_NAME = 'SUC_CHAVE')) < 100)
    BEGIN
        ALTER TABLE SUC_CONFIG ALTER COLUMN [SUC_CHAVE] VARCHAR(100) NULL
    END
GO  

Answer 4

Taking advantage of the cue, DATALENGTH has varied behaviors, depending on the type of data you use, example:

DECLARE @exemploChar char(30)
SET @exemploChar = 'stack'

SELECT DATALENGTH(@exemploChar)

In the above example, the column, exemplified by the variable @exemploChar, will receive the total size determined in the column, because the data type char has fixed size, that is, it will occupy 30 bytes (1 byte per character), even if the column content, occupy, as in our example, only 5 characters.

DECLARE @exemploVarchar varchar(30)
SET @exemploVarchar = 'stack'

SELECT DATALENGTH(@exemploVarchar)

In the above example, we create the column, exemplified by the variable @exemploVarchar, with the type of data varchar. As this is a variable data type, the size used was only 5 bytes (5 characters) and not the 30 (1 byte per character) predetermined in the creation.

Out of curiosity, I’ll do two more examples, but using Unicode.

DECLARE @exemploNchar nchar(30)
SET @exemploNchar = 'stack'

SELECT DATALENGTH(@exemploNchar)

For this, we can notice that the column size has doubled to 60 bytes consumed. This is because the Unicode data type consumes two bytes per character and not one.

DECLARE @exemploNvarchar nvarchar(30)
SET @exemploNvarchar = 'stack'

SELECT DATALENGTH(@exemploNvarchar)

Finally, in this example the consumption will be 10 bytes (2 bytes per character).

In case you’re wondering, when to use one or the other:

• char is good for writing, because the column size is already determined, there is no need for extra processing for this task.
• varchar is good for reading, because in 90% of cases, the column size will be smaller than the predetermined size
• unicode It is good to be used when you are importing data from another database or need to treat special characters in our language. In this case, the Unicode does not try to make the conversion, keeping the original data, in addition to gaining speed.

Remember that if you want to see the properties of a table, and in it see the size determined for each column, you can use the commands below:

• sp_help [nome_objeto]*

  *object must be in the same schema used by login

• select * from INFORMATION_SCHEMA.COLUMNS

Select the object and with the keyboard, press ALT + F1

Answer 5

From what I understood the solution is to consult the metadata table and see the definition of the type and size of the field.

These tables, of course, change according to the database. For more details, see the documentation.