How to Determine a SQL Column's Data Type Using INFORMATION_SCHEMA?

SQL Statement to Determine Column Data Type

In the realm of database management, understanding the data types associated with table columns is crucial. SQL provides a comprehensive view into the structure and properties of tables. This includes the ability to retrieve information about column data types using a specific SQL statement.

INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view serves as a powerful tool for querying metadata about tables within a database. This view exposes various details about columns, including their data types. By utilizing the DATA_TYPE column, you can determine the data type of a specific column.

Parameterization Considerations

Note that the DATA_TYPE column in INFORMATION_SCHEMA.COLUMNS may not always provide all the necessary information for parameterized data types. For example, it doesn't include arguments for parameters, which can lead to potential column behavior issues.

To address this, it's recommended to consult additional columns within the INFORMATION_SCHEMA.COLUMNS view, such as:

• CHARACTER_OCTET_LENGTH (for binary types)
• CHARACTER_MAXIMUM_LENGTH (for character and national character types)
• DATETIME_PRECISION (for datetime2 and datetimeoffset types)
• NUMERIC_PRECISION and NUMERIC_SCALE (for decimal and numeric types)

By combining these columns, you can reconstruct the complete parameterization for the data type.

Sample Query

The following SQL query demonstrates how to retrieve the full data type information for a specified column using the INFORMATION_SCHEMA.COLUMNS view:

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;

Sample Output

Executing the query would return a comprehensive description of the data type for the specified column, including its parameters and nullability. Here's an example output:

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