1

I have a SQL Server with numeric types for few columns.

When getting a table structure, I have the following results:

Query:

    SELECT * 
FROM DB.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TABLE' 
  AND DATA_TYPE = 'numeric' 

The result is the following (truncated):

CHARACTER_MAXIMUM_LENGTH  NUMERIC_PRECISION   NUMERIC_SCALE
NULL                     2                   0

Will the value CHARACTER_MAXIMUM_LENGTH always be NULL for a numeric ? Don't scale and precision impact the character length of the value ?

1

From this wonderful website:

INFORMATION_SCHEMA.COLUMNS

CHARACTER_MAXIMUM_LENGTH

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. For more information, see

Data Types (Transact-SQL).

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. For more information, see Data Types (Transact-SQL).

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 or text 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.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.