How to get the size of a varchar[n] field in one SQL statement?

Question

Suppose that I have a SQL table that has a varchar[1000] field called "Remarks".

I would like to craft a single SQL statement, which when executed, will return 1000, or whatever the size of the varchar field might be changed to in the future.

Something like SELECT size(Remarks) FROM mytable.

How do I do this?

This will depend on the RDBMS you are working on. But as Neil said you may have to search in the information scheme of your server. — Marc Bouvier, Apr 18 '11 at 15:39

score 152 · Accepted Answer · answered Apr 18 '11 at 15:36

152

select column_name, data_type, character_maximum_length    
  from information_schema.columns  
 where table_name = 'myTable'

answered Apr 18 '11 at 15:36

Neil Knight

47,437
25
129
188

9

you could specify additional clause `AND column_name = 'Remarks'` if you want to filter only one column out. – Sanjeevakumar Hiremath Apr 18 '11 at 15:38
4

Just FYI, this is a MySQL specific query. – pickypg Apr 18 '11 at 15:38
@pickypg: The question is for SQL Server 2008. – Neil Knight Apr 18 '11 at 15:42
10

`INFORMATION_SCHEMA` is an ANSI standard thing - not MySQL or SQL Server specific at all ! – marc_s Apr 18 '11 at 15:50
to filter by a particular database schema add: `AND table_schema = 'mydatabase'` – user12345 Jul 03 '14 at 19:23

score 33 · Answer 2 · edited Feb 02 '15 at 10:52

33

On SQL Server specifically:

SELECT DATALENGTH(Remarks) AS FIELDSIZE FROM mytable

Documentation

edited Feb 02 '15 at 10:52

UnDiUdin

14,924
39
151
249

answered Apr 18 '11 at 15:38

MarcE

3,586
1
23
27

4

This statement returns the length of the data that has already been input. I want the size of the data that the field will hold. – Vivian River Apr 18 '11 at 15:41
1

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Apr 18 '11 at 15:49

score 18 · Answer 3 · answered Aug 24 '17 at 18:53

For SQL Server (2008 and above):

SELECT COLUMNPROPERTY(OBJECT_ID('mytable'), 'Remarks', 'PRECISION');

COLUMNPROPERTY returns information for a column or parameter (id, column/parameter, property). The PRECISION property returns the length of the data type of the column or parameter.

COLUMNPROPERTY documentation

Greg Quinn · Answer 4 · 2016-08-08T22:32:58.783

10

This will work on SQL SERVER...

SELECT COL_LENGTH('Table', 'Column')

edited Aug 08 '16 at 22:32

answered Jul 29 '16 at 06:12

Greg Quinn

1,927
1
23
26

4

Worth noting that this will return the length in *bytes* so you'll have to halve it for `NVARCHAR` or `NCHAR` for example. See https://learn.microsoft.com/en-us/sql/t-sql/functions/col-length-transact-sql – Bernhard Hofmann Jun 21 '17 at 08:56

score 1 · Answer 5 · answered Jun 01 '15 at 02:46

1

I was looking for the TOTAL size of the column and hit this article, my solution is based off of MarcE's.

SELECT sum(DATALENGTH(your_field)) AS FIELDSIZE FROM your_table

answered Jun 01 '15 at 02:46

ProVega

5,864
2
36
34

Ihor Konovalenko · Answer 6 · 2020-01-18T17:44:41.713

This is a function for calculating max valid length for varchar(Nn):

CREATE FUNCTION [dbo].[GetMaxVarcharColumnLength] (@TableSchema NVARCHAR(MAX), @TableName NVARCHAR(MAX), @ColumnName VARCHAR(MAX))
RETURNS INT
AS
BEGIN
    RETURN (SELECT character_maximum_length FROM information_schema.columns  
            WHERE table_schema = @TableSchema AND table_name = @TableName AND column_name = @ColumnName);
END

Usage:

IF LEN(@Name) > [dbo].[GetMaxVarcharColumnLength]('person', 'FamilyStateName', 'Name') 
            RETURN [dbo].[err_Internal_StringForVarcharTooLong]();

score 1 · Answer 7 · answered Jul 24 '17 at 14:12

1

select column_name, data_type, character_maximum_length    
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'Table1'

answered Jul 24 '17 at 14:12

Bengi Besçeli

3,638
12
53
87

score 0 · Answer 8 · answered Oct 27 '16 at 18:09

For t-SQL I use the following query for varchar columns (shows the collation and is_null properties):

SELECT
    s.name
    , o.name as table_name
    , c.name as column_name
    , t.name as type
    , c.max_length
    , c.collation_name
    , c.is_nullable
FROM
    sys.columns c
    INNER JOIN sys.objects o ON (o.object_id = c.object_id)
    INNER JOIN sys.schemas s ON (s.schema_id = o.schema_id)
    INNER JOIN sys.types t ON (t.user_type_id = c.user_type_id)
WHERE
    s.name = 'dbo'
    AND t.name IN ('varchar') -- , 'char', 'nvarchar', 'nchar')
ORDER BY
    o.name, c.name

Please be aware that this will display the max_length in bytes, meaning you'll get 'double' sizes for NVARCHAR columns. — Jens, Aug 29 '19 at 09:21

score 0 · Answer 9 · answered Mar 24 '21 at 16:23

0

For MS SQL Server this will return column length:

SELECT COL_LENGTH('dbo.mytable', 'Remarks') AS Result;

answered Mar 24 '21 at 16:23

Nenad Bulatović

7,238
14
83
113

Vipin · Answer 10 · 2022-08-01T10:13:29.143

0

For SQL Server, instead of SIZE we can use LEN rest everything given in question is fine. LEN returns number of characters, not sure I should call it eye friendly or brain friendly :)

SELECT LEN(Remarks) FROM mytable

DATALENGTH also works fine as given in other answers but it returns number of bytes.

SELECT DATALENGTH(Remarks) FROM mytable

Here is documentation reference

edited Aug 01 '22 at 10:13

answered Aug 01 '22 at 10:07

Vipin

4,851
3
35
65

How to get the size of a varchar[n] field in one SQL statement?

10 Answers10

Linked

Related