How to Retrieve Complete Column Data Types Including Parameters and Nullability in SQL?

How to Retrieve Column Type Using SQL

In modern relational database management systems (RDBMS), obtaining column type information for a specific table is a common requirement. To address this need, the INFORMATION_SCHEMA.COLUMNS view, widely supported in ISO SQL, provides a standardized mechanism to retrieve column metadata, including their data types.

The DATA_TYPE column within the INFORMATION_SCHEMA.COLUMNS view displays the base T-SQL/SQL Server type names, such as nvarchar, datetime2, and decimal. However, it excludes parameter arguments for parameterized types. This omission can lead to unintended column behavior.

To mitigate this issue, it's essential to leverage additional columns, such as CHARACTER_OCTET_LENGTH (for binary), CHARACTER_MAXIMUM_LENGTH (for char and nchar), DATETIME_PRECISION (for datetime2 and datetimeoffset), and NUMERIC_PRECISION and NUMERIC_SCALE (for decimal and numeric). By combining these values, you can reconstruct the complete type, including its parameters.

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 yields results that include the complete data type, including parameter information and nullability, as illustrated below:

[Image of query results]

The above is the detailed content of How to Retrieve Complete Column Data Types Including Parameters and Nullability in SQL?. For more information, please follow other related articles on the PHP Chinese website!