SQL 服务器查询以获取表中的列列表以及数据类型、NOT NULL 和 PRIMARY KEY 约束

2021-12-05 00:00:00 sql database sql-server-2005 sql-server custom-data-type

我需要在 SQL 服务器上编写一个查询以获取特定表中的列列表、其关联的数据类型(带长度)以及它们是否不为空.我已经成功做到了这么多.

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.

相关文章