SQL Server 2008:找出表中的主键/外键? [英] SQL Server 2008: Find out primary/foreign key in Table?
问题描述
有谁知道我怎么能看到哪些是主要的&表中的外键?
Does anyone know how I can see which are the primary & foreign keys in a table?
感谢所有回复.我正在寻找一个 SQL 查询来做到这一点.现在我正在编写一个工具,它可以列出数据库的所有表并显示列.我还想显示哪些键是主键.
Thanks for all the responses. I was looking for a SQL Query to do that. Right now I'm playing around with writing a tool which can list me all Tables of a DB and show the columns. I'd like to display also which of the keys are primary keys.
这是我读出表格目录的方式:
This is how I read out the Table Catalog:
const string sqlSelectTable = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
"FROM INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_TYPE = 'BASE TABLE' " +
"ORDER BY TABLE_TYPE,TABLE_NAME";
这就是我获取列信息的方式:
And this is how I get the Infos about a Column:
const string sqlSelectTable =
"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH " +
"FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE (TABLE_NAME = @TABLE_NAME) " +
"ORDER BY ORDINAL_POSITION";
我是否必须创建一个内连接来查看哪些列是主键?
Would I have to create a Inner-Join so see which of the Columns are Primary Key?
干杯
推荐答案
对于每个表的主键,可以使用这个查询:
For the primary key on each table, you can use this query:
SELECT
kc.name,
c.NAME
FROM
sys.key_constraints kc
INNER JOIN
sys.index_columns ic ON kc.parent_object_id = ic.object_id
INNER JOIN
sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
kc.type = 'PK'
对于外键,我相信这个查询应该为您提供必要的信息:
and for the foreign key, I believe this query should get you the necessary information:
SELECT
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM
sys.foreign_key_columns fkc
INNER JOIN
sys.columns c
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.object_id
INNER JOIN
sys.columns cref
ON fkc.referenced_column_id = cref.column_id
AND fkc.referenced_object_id = cref.object_id
马克
这篇关于SQL Server 2008:找出表中的主键/外键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!