How Can I Retrieve the Precise Data Type of a SQL Column Including Parameters and Nullability?

Get Column Type with SQL

In various database scenarios, understanding the data type of a specific column in a table is crucial. Fortunately, SQL offers a comprehensive solution to retrieve column types through the INFORMATION_SCHEMA.COLUMNS view. This view provides metadata about database tables and their columns, including the coveted data type information.

The DATA_TYPE column in INFORMATION_SCHEMA.COLUMNS returns the base data type of the column. However, for some data types with parameterization options like nvarchar or datetime2, the base type alone may be insufficient to fully describe the column's characteristics.

To address this issue, additional columns in the view can be consulted. For example, CHARACTER_OCTET_LENGTH for binary and varbinary, CHARACTER_MAXIMUM_LENGTH for char, nchar, varchar, and nvarchar, DATETIME_PRECISION for datetime2 and datetimeoffset, and NUMERIC_PRECISION and NUMERIC_SCALE for decimal and numeric data types provide the necessary information to accurately reconstruct the parameter values.

By referencing these supplementary columns and combining their values with the base data type, you can obtain a precise description of the column's type. The following example query demonstrates this process:

WITH q AS (
    SELECT
        c.TABLE_SCHEMA,
        c.TABLE_NAME,
        c.ORDINAL_POSITION,
        c.COLUMN_NAME,
        c.DATA_TYPE,
        CASE
            WHEN c.DATA_TYPE IN ( N'binary', N'varbinary'                    ) THEN ( CASE c.CHARACTER_OCTET_LENGTH   WHEN -1 THEN N'(max)' ELSE CONCAT( N'(', c.CHARACTER_OCTET_LENGTH  , N')' ) END )
            WHEN c.DATA_TYPE IN ( N'char', N'varchar', N'nchar', N'nvarchar' ) THEN ( CASE c.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN N'(max)' ELSE CONCAT( N'(', c.CHARACTER_MAXIMUM_LENGTH, N')' ) END )
            WHEN c.DATA_TYPE IN ( N'datetime2', N'datetimeoffset'            ) THEN CONCAT( N'(', c.DATETIME_PRECISION, N')' )
            WHEN c.DATA_TYPE IN ( N'decimal', N'numeric'                     ) THEN CONCAT( N'(', c.NUMERIC_PRECISION , N',', c.NUMERIC_SCALE, N')' )
        END AS DATA_TYPE_PARAMETER,
        CASE c.IS_NULLABLE
            WHEN N'NO'  THEN N' NOT NULL'
            WHEN N'YES' THEN     N' NULL'
        END AS IS_NULLABLE2
    FROM
        INFORMATION_SCHEMA.COLUMNS AS c
)
SELECT
    q.TABLE_SCHEMA,
    q.TABLE_NAME,
    q.ORDINAL_POSITION,
    q.COLUMN_NAME,
    CONCAT( q.DATA_TYPE, ISNULL( q.DATA_TYPE_PARAMETER, N'' ), q.IS_NULLABLE2 ) AS FULL_DATA_TYPE

FROM
    q
WHERE
    q.TABLE_SCHEMA = 'yourSchemaName' AND
    q.TABLE_NAME   = 'yourTableName'  AND 
    q.COLUMN_NAME  = 'yourColumnName'

ORDER BY
    q.TABLE_SCHEMA,
    q.TABLE_NAME,
    q.ORDINAL_POSITION;

This query meticulously assembles the complete data type information, including parameter values and nullability status, into the FULL_DATA_TYPE column. By executing this query, you can retrieve the exact data type of a specific column and gain a deeper understanding of your database schema.

The above is the detailed content of How Can I Retrieve the Precise Data Type of a SQL Column Including Parameters and Nullability?. For more information, please follow other related articles on the PHP Chinese website!