SQL Server查询以获取表中的列列表以及数据类型，NOT NULL和PRIMARY KEY约束 [英] SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

问题描述

我需要在SQL Server上编写查询以获取特定表中的列的列表，其关联的数据类型(带有长度)以及它们是否不为null.而且我已经做到了很多.

I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much.

但是现在我还需要在同一个表中针对一列-如果该列是主键，则为 TRUE .

But now i also need to get, in the same table, against a column - TRUE if that column is a primary key.

我该怎么做?

我的预期输出是:

Column name | Data type | Length | isnull | Pk

推荐答案

为避免某些列重复行，请使用user_type_id而不是system_type_id.

To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) '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')

只需将 YourTableName 替换为您的实际表名-适用于SQL Server 2005及更高版本.

Just replace YourTableName with your actual table name - works for SQL Server 2005 and up.

如果使用的是架构，请将 YourTableName 替换为 YourSchemaName.YourTableName ，其中 YourSchemaName 是实际的架构名称，而 YourTableName 是实际的表名.

In case you are using schemas, replace YourTableName by YourSchemaName.YourTableName where YourSchemaName is the actual schema name and YourTableName is the actual table name.

这篇关于SQL Server查询以获取表中的列列表以及数据类型，NOT NULL和PRIMARY KEY约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！