none
Microsoft SQL Server INFORMATION_SCHEMA.COLUMNS CHARACTER_MAXIMUM_LENGTH is NULL

    Question

  • I am trying to get maximum allowed column length (characters count in representation, not bytes count) from MS SQL from INFORMATION_SCHEMA.COLUMNS table. I have found that CHARACTER_MAXIMUM_LENGTH can be NULL for numeric and datetime types. I also have found three following columns in INFORMATION_SCHEMA.COLUMNS table:

    1. NUMERIC_PRECISION;
    2. NUMERIC_SCALE (related to NUMERIC_PRECISION);
    3. DATETIME_PRECISION;

    As i understand there is always at least on non NULL value of column exists, so we can use that columns to get maximum length. I can`t found clear specification about this columns usage. For example lets say i have DATETIME_PRECISION equals 3 for datetime type, how i can calculate characters count from that '3' using only INFORMATION_SCHEMA tables?

    Thursday, July 25, 2013 7:34 AM

All replies

  • Hi Konstantyn,

    CHARACTER_MAXIMUM_LENGTH, in characters, for binary data, character data, or text and image data.

    the below table can help you

    Column name

    Data type

    Description

    TABLE_CATALOG

    nvarchar(128)

    Table qualifier.

    TABLE_SCHEMA

    nvarchar(128)

    Name of schema that contains the table.

    TABLE_NAME

    nvarchar(128)

    Table name.

    COLUMN_NAME

    nvarchar(128)

    Column name.

    ORDINAL_POSITION

    int

    Column identification number.

    Note: In SQL Server 2005, these column IDs are consecutive numbers.

    COLUMN_DEFAULT

    nvarchar(4000)

    Default value of the column.

    IS_NULLABLE

    varchar(3)

    Nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned.

    DATA_TYPE

    nvarchar(128)

    System-supplied data type.

    CHARACTER_MAXIMUM_LENGTH

    int

    Maximum length, in characters, for binary data, character data, or text and image data.

    -1 for xml and large-value type data. Otherwise, NULL is returned.

    CHARACTER_OCTET_LENGTH

    int

    Maximum length, in bytes, for binary data, character data, or text and image data.

    -1 for xml and large-value type data. Otherwise, NULL is returned.

    NUMERIC_PRECISION

    tinyint

    Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

    NUMERIC_PRECISION_RADIX

    smallint

    Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

    NUMERIC_SCALE

    int

    Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

    DATETIME_PRECISION

    smallint

    Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.

    CHARACTER_SET_CATALOG

    nvarchar(128)

    Returns master. This indicates the database in which the character set is located, if the column is character data ortext data type. Otherwise, NULL is returned.

    CHARACTER_SET_SCHEMA

    nvarchar(128)

    Always returns NULL.

    CHARACTER_SET_NAME

    nvarchar(128)

    Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.

    COLLATION_CATALOG

    nvarchar(128)

    Always returns NULL.

    COLLATION_SCHEMA

    nvarchar(128)

    Always returns NULL.

    COLLATION_NAME

    nvarchar(128)

    Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned.

    DOMAIN_CATALOG

    nvarchar(128)

    If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned.

    DOMAIN_SCHEMA

    nvarchar(128)

    If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned.

    DOMAIN_NAME

    nvarchar(128)

    If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned.

    Good Luck!

    Thursday, July 25, 2013 7:56 AM
  •  

    Hi,

    According to the MSDN,the meaning of CHARACTER_MAXIMUM_LENGTH  is

    Maximum length, in characters, for binary data, character data, or text and image data.

    -1 for xml and large-value type data. Otherwise, NULL is returned.

    Example:

    If there is a table named MyTable has 3 columns:

    1. Id (PK,int,not null)

    2.name(varchar(100),not null)

    3.remark(nvarchar(50),not null).

    You can use the SQL statement to query the information of the columns.

    SELECT TABLE_NAME, COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH 
    FROM TestDataBase.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable';
    Then the results showed below.

    TABLE_NAME   COLUMN_NAME      CHARACTER_MAXIMUM_LENGTH
    MyTable           Id                         NULL
    MyTable            name                   100
    MyTable            remark                 50

     

    For more information, see

    COLUMNS (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms188348(v=sql.105).aspx

    Data Types (Transact-SQL).

    http://msdn.microsoft.com/en-us/library/ms187752(v=sql.105).aspx

    Best Regards.

     

    Thursday, July 25, 2013 9:38 AM