How to Retrieve Column Information, Data Types, and Primary Keys in SQL Server?

Getting Column Information, Data Types, Null Constraints, and Primary Keys in SQL Server

In SQL Server, you can retrieve detailed information about the columns in a specific table, including their data types, length, nullability, and whether they are primary keys. Here's how you can achieve this:

1. Select Necessary Columns:
   Begin your query by selecting the following columns:

   • Column name: Use c.name to get the column name.
   • Data type: Use t.name to retrieve the data type of the column.
   • Length: For strings and other data types with a defined length, use c.max_length.
   • Precision and Scale: For numeric data types, include c.precision and c.scale.
   • Nullable status: Use c.is_nullable to determine if the column allows null values.
2. Join Necessary Tables:
   Join the sys.columns table (aliased as c) with the sys.types table (aliased as t) based on their user_type_id to retrieve column-specific information.
3. Check for Primary Key Constraints:
   Optionally, you can check for primary key constraints by joining with the sys.index_columns (ic) and sys.indexes (i) tables. If a column has an index with is_primary_key set to 1, it is a primary key. Use ISNULL(i.is_primary_key, 0) to handle cases where the column is not a primary key.
4. Filter by Table Name:
   Use WHERE c.object_id = OBJECT_ID('YourTableName') to filter the results for a specific table, replacing 'YourTableName' with the actual table name.
5. Replace Table Name:
   For schemas, replace 'YourTableName' with 'YourSchemaName.YourTableName'.

Example Query:

SELECT 
    c.name AS 'Column Name',
    t.Name AS 'Data type',
    c.max_length AS 'Max Length',
    c.precision,
    c.scale,
    c.is_nullable AS 'Null?',
    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')

Output:

The query will return a table with the following information:

Column Name	Data type	Max Length	Null?	Primary Key

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