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?
select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'myTable' 
AND column_name = 'Remarks' if you want to filter only one column out.
INFORMATION_SCHEMA is an ANSI standard thing - not MySQL or SQL Server specific at all !
AND table_schema = 'mydatabase'
{ } ) on the editor toolbar to nicely format and syntax highlight it!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.
This will work on SQL SERVER...
SELECT COL_LENGTH('Table', 'Column') 
NVARCHAR or NCHAR for example. See learn.microsoft.com/en-us/sql/t-sql/functions/…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 
select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'Table1' 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](); 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 
For MS SQL Server this will return column length:
SELECT COL_LENGTH('dbo.mytable', 'Remarks') AS Result; 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 