How to Retrieve Detailed Table Column Information in SQL Server?

Retrieving Table Column Information with Data Types, Constraints, and Primary Key Flags

To obtain a detailed list of columns in a table along with their data types, whether they allow null values, and whether they are primary keys, the following SQL Server query can be used:

SELECT 
    c.name AS 'Column Name',
    t.Name AS 'Data Type',
    c.max_length AS 'Max Length',
    c.precision,
    c.scale,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) AS 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

In this query, replace 'YourTableName' with the actual name of the target table. If the table is located in a schema, replace 'YourTableName' with '(YourSchemaName}.YourTableName)'.

Explanation:

• The columns table contains information about the columns in a table.
• The types table provides details about the data types of the columns.
• The index_columns and indexes tables capture the primary keys for columns.
• The LEFT OUTER JOINs retrieve the primary key flag if it exists; otherwise, they return 0.
• The query presents the results in a clear and structured format, including column names, data types, lengths, nullability, and primary key status.

The above is the detailed content of How to Retrieve Detailed Table Column Information in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!