How to get the size of a varchar[n] field in one SQL statement?
SqlSql Server-2008VarcharSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
select column_name, data_type, character_maximum_length
from information_schema.columns
where table_name = 'myTable'
Solution 2 - Sql
Solution 3 - Sql
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][1]
[1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql "COLUMNPROPERTY documentation"
Solution 4 - Sql
This will work on SQL SERVER...
SELECT COL_LENGTH('Table', 'Column')
Solution 5 - Sql
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
Solution 6 - Sql
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]();
Solution 7 - Sql
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'Table1'
Solution 8 - Sql
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
Solution 9 - Sql
For MS SQL Server this will return column length:
SELECT COL_LENGTH('dbo.mytable', 'Remarks') AS Result;