How do I retrieve the VarChar limit for a SQL column?

Question

I need to find a way to retrieve the limit itself, not the longest data entry stored under that column, which is what most of my searching has turned up.

For instance, if I executed the following statement:

ALTER TABLE TestTable
Add TestColumn VarChar(500)

is there a way to construct a selector to return the character limit (500) itself?

Whenever I use a selector for the data_type from Information_schecma.columns (below), it just returns "varchar", without returning how many characters go with it.

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'TestTable' AND 
     COLUMN_NAME = 'TestColumn'

You have to return the `CHARACTER_MAXIMUM_LENGTH` field with the `DATA_TYPE` — vol7ron, May 11 '18 at 15:41

score 0 · Answer 1 · answered May 11 '18 at 15:38

You are looking for max_length in sys.columns

SELECT c.max_length 
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'testtable'
AND c.name = 'testcolumn'

Or character_maximum_length in information schema.columns

SELECT character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'TestTable' AND 
     COLUMN_NAME = 'TestColumn'

How do I retrieve the VarChar limit for a SQL column?

1 Answers1