CODEWITHSUNDEEP
sqlsql-server-2008varchar
Vivian River
Vivian River

Reputation: 32390

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

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?

Upvotes: 107

Views: 231852

Answers (10)

Vipin
Vipin

Reputation: 5223

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

Upvotes: 0

Nenad Bulatović
Nenad Bulatović

Reputation: 7434

For MS SQL Server this will return column length:

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

Upvotes: 0

Ihor Konovalenko
Ihor Konovalenko

Reputation: 1407

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]();

Upvotes: 1

Frank
Frank

Reputation: 713

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

Upvotes: 20

Bengi Besceli
Bengi Besceli

Reputation: 3748

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

Upvotes: 1

Andrei Sura
Andrei Sura

Reputation: 2604

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

Upvotes: 0

Greg Quinn
Greg Quinn

Reputation: 2178

This will work on SQL SERVER...

SELECT COL_LENGTH('Table', 'Column')

Upvotes: 10

ProVega
ProVega

Reputation: 5914

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

Upvotes: 1

MarcE
MarcE

Reputation: 3731

On SQL Server specifically: 

SELECT DATALENGTH(Remarks) AS FIELDSIZE FROM mytable

Documentation

Upvotes: 39

Neil Knight
Neil Knight

Reputation: 48537

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

Upvotes: 158

Related Questions