如何列出SQL Server用户定义的表类型的主键?

My question is simple, how do you list the primary key (column name) of a SQL Server User Defined Table Type?

我的问题很简单，如何列出SQL Server用户定义的表类型的主键(列名)?

ex;

交货;

CREATE TYPE [dbo].[MyTableType] AS TABLE
(
    [ID] int NOT NULL, PRIMARY KEY CLUSTERED ( [ID])
)

How to get the column [ID] with a query

如何使用查询获取列[ID]

It seem it is only possible to find primary key for real table, not table type.

似乎只能找到真正的表的主键，而不是表类型。

2 个解决方案

#1

This is stored in the catalog views:

它存储在目录视图中:

SELECT  c.Name
FROM    sys.table_types AS tt
        INNER JOIN sys.key_constraints AS kc
            ON kc.parent_object_id = tt.type_table_object_id
        INNER JOIN sys.indexes AS i
            ON i.object_id = kc.parent_object_id
            AND i.index_id = kc.unique_index_id
        INNER JOIN sys.index_columns AS ic
            ON ic.object_id = kc.parent_object_id
        INNER JOIN sys.columns AS c
            ON c.object_id = ic.object_id
            AND c.column_id = ic.column_id
WHERE   tt.Name = 'YourTypeName';

#2

A user-defined table isn't an actual table so it has an entry in sys.table_types, not in sys.tables.

用户定义的表不是实际的表，因此在sys中有一个条目。在sys.tables table_types,不是。

The key information can be retrieved from sys.key_constraints as with other tables, if the sys.table_types.type_table_object_id and sys.key_constraints.parent_object_id fields are used, eg:

关键信息可以从sys中检索。key_constraints和其他表一样，如果是sys.table_types。type_table_object_id sys.key_constraints。使用parent_object_id字段，例如:

create TYPE TestTableType AS TABLE 
( 
    ID int primary key,
    Name nVARCHAR(50)
)

declare @typeID int

select @typeId=type_table_object_id 
from sys.table_types
where name='TestTableType'

select @typeId
-- Returns 1134627085

select * 
from sys.key_constraints
where parent_object_id=@typeID

-- Returns 
-- PK__TT_TestT__3214EC27BA14A4A6   1150627142  NULL    4   1134627085  PK  PRIMARY_KEY_CONSTRAINT  2016-04-25 17:36:34.890 2016-04-25 17:36:34.890 1   0   0   1   1

After that, you can get the column name in the same way as with other primary keys, by joining with sys.index_columns and sys.columns:

之后，您可以通过与sys的连接，以与其他主键相同的方式获得列名。index_columns sys.columns:

select col.name
from sys.key_constraints kcon
    inner join sys.index_columns indcol on indcol.object_id=kcon.parent_object_id
    inner join sys.columns col on col.object_id = kcon.parent_object_id 
               and col.column_id = indcol.column_id
where parent_object_id=@typeID

或

select col.name
from sys.table_types tt 
    inner join sys.key_constraints kcon on type_table_object_id=kcon.parent_object_id
    inner join sys.index_columns indcol on indcol.object_id=kcon.parent_object_id
    inner join sys.columns col on col.object_id = kcon.parent_object_id and col.column_id = indcol.column_id
where tt.name='TestTableType'

#1