How to Get the Complete Data Type of a SQL Column Including Parameters?-Mysql Tutorial-php.cn

How to Get the Complete Data Type of a SQL Column Including Parameters?

DDD

Release： 2024-12-27 18:58:14

Original

387 people have browsed it

How to Get the Complete Data Type of a SQL Column Including Parameters?

Get Column Type in SQL

To obtain the data type of a specific column in a table, you can utilize the INFORMATION_SCHEMA.COLUMNS system view. This view is supported in ISO SQL, including SQL Server.

The DATA_TYPE column within the INFORMATION_SCHEMA.COLUMNS view provides the basic SQL Server/T-SQL type names. However, note that this information may not include arguments for parameterized types, which can lead to unexpected behavior in some cases.

For instance, consider three columns with data types nvarchar(max), datetime2(3), and decimal(10,5). The corresponding INFORMATION_SCHEMA.COLUMNS output will only show nvarchar, datetime2, and decimal.

To fully reconstruct the type's parameter values, it's recommended to examine additional columns from the INFORMATION_SCHEMA.COLUMNS view:

CHARACTER_OCTET_LENGTH (for binary)
CHARACTER_MAXIMUM_LENGTH (for char and nchar)
DATETIME_PRECISION (for datetime2 and datetimeoffset)
NUMERIC_PRECISION and NUMERIC_SCALE (for decimal and numeric)

For example, the following SQL query demonstrates how to retrieve the detailed data type for a specific column:

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;

Copy after login

This query returns results in the following format:

TABLE_SCHEMA | TABLE_NAME | ORDINAL_POSITION | COLUMN_NAME | FULL_DATA_TYPE
yourSchemaName | yourTableName | 1 | yourColumnName | nvarchar(max) NOT NULL

Copy after login

The above is the detailed content of How to Get the Complete Data Type of a SQL Column Including Parameters?. For more information, please follow other related articles on the PHP Chinese website!