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

User · Accepted Answer

select column name  data type  character maximum length       from information schema columns    where table name    myTable

User · Answer

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   gt   dbo   GetMaxVarcharColumnLength   person    FamilyStateName    Name                RETURN  dbo   err Internal StringForVarcharTooLong

User · Answer

On SQL Server specifically    SELECT DATALENGTH Remarks  AS FIELDSIZE FROM mytable   Documentation

User · Answer

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

User · Answer

This will work on SQL SERVER     SELECT COL LENGTH  Table    Column

User · Answer

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

User · Answer

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

User · Answer

select column name  data type  character maximum length     from INFORMATION SCHEMA COLUMNS where table name    Table1

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

Examples related to sql

Examples related to sql-server-2008

Examples related to varchar