当前位置：文江博客话题详情

SQL Server 中是否有只列出主键的视图？

发布于 2024-09-24 06:06:02 字数 159 浏览 8 评论 0原文

我正在使用 SQL Server 并尝试做一些“反思”，如果你愿意的话。我找到了系统视图 sys.identity_columns，它包含我所有表的所有标识列。

但是，我需要能够选择有关非标识列的主键的信息。是否有包含所有主键数据且仅包含主键数据的视图？如果没有，我还能如何获取这些数据？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

桃气十足 2024-10-01 06:06:02

这适用于 SQL Server 2005 及更高版本：

select OBJECT_SCHEMA_NAME(i.object_id), OBJECT_NAME(i.object_id), i.name
from sys.indexes i
where i.is_primary_key = 1
order by 1, 2, 3

This works for SQL Server 2005 and higher:

select OBJECT_SCHEMA_NAME(i.object_id), OBJECT_NAME(i.object_id), i.name
from sys.indexes i
where i.is_primary_key = 1
order by 1, 2, 3

回复收藏 0 原文

穿越时光隧道 2024-10-01 06:06:02

SELECT name FROM sys.key_constraints WHERE type = 'PK';
SELECT name FROM sys.key_constraints WHERE type = 'UQ';

SELECT name FROM sys.key_constraints WHERE type = 'PK';
SELECT name FROM sys.key_constraints WHERE type = 'UQ';

回复收藏 0 原文

ㄟ。诗瑗 2024-10-01 06:06:02

我意识到该问题已被标记为已回答，但对某些人展示如何合并 sys.index_columns 可能也会有所帮助（除了 sys.indexes 之外）到您的查询，以便将实际的主键索引与表的列联系起来。例子：

select
    t.Name as tableName
    ,c.name as columnName
    ,case when pk.is_primary_key is not null then 1 else 0 end as isPrimaryKeyColumn
from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
left join sys.index_columns pkCols 
    on t.object_id = pkCols.object_id 
    and c.column_id = pkCols.column_id
left join sys.indexes pk 
    on pkCols.object_id = pk.object_id 
    and pk.is_primary_key = 1
where 
    t.name = 'MyTable'

i realize that the question has already been marked as answered, but it might also be helpful to some to show how to incorporate sys.index_columns (in addition to sys.indexes) to your query in order to tie the actual primary key index to the table's columns. example:

select
    t.Name as tableName
    ,c.name as columnName
    ,case when pk.is_primary_key is not null then 1 else 0 end as isPrimaryKeyColumn
from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
left join sys.index_columns pkCols 
    on t.object_id = pkCols.object_id 
    and c.column_id = pkCols.column_id
left join sys.indexes pk 
    on pkCols.object_id = pk.object_id 
    and pk.is_primary_key = 1
where 
    t.name = 'MyTable'

回复收藏 0 原文

甜嗑 2024-10-01 06:06:02

试试这个...

SELECT KC.TABLE_NAME, KC.COLUMN_NAME, KC.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC
WHERE OBJECTPROPERTY(OBJECT_ID(KC.CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0

Try this...

SELECT KC.TABLE_NAME, KC.COLUMN_NAME, KC.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC
WHERE OBJECTPROPERTY(OBJECT_ID(KC.CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0

回复收藏 0 原文

~没有更多了~