How to Retrieve the Complete Data Type of a SQL Column?

How to Retrieve Column Type in SQL

In the realm of database management, it is imperative to have information about the structure of tables, including the data types of their columns. This knowledge aids in data comprehension, manipulation, and query optimization. Thankfully, SQL provides a mechanism to retrieve the data type of a specific column.

Utilizing the INFORMATION_SCHEMA.COLUMNS View

The INFORMATION_SCHEMA.COLUMNS view provides a comprehensive catalog of table metadata, including column types. This view offers a wealth of information, including the column's data type, nullability, and other attributes. To retrieve the data type of a particular column, use the following query:

SELECT
    DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = '[yourSchemaName]' AND
    TABLE_NAME = '[yourTableName]' AND
    COLUMN_NAME = '[yourColumnName]';

Reconstructing Types with Parameters

While the DATA_TYPE column provides essential information, it may not always include the full specification of parameterized types. For example, a column typed as nvarchar(max) will only show as nvarchar. To address this, consider the following approach:

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
    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]';

This query reconstructs the full data type specification, including parameters for parameterized types, and adds a descriptive nullability indicator.

Utilizing these techniques, you can successfully retrieve and understand the data types of columns in your SQL tables.

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