Does anyone know how I can see which are the primary & foreign keys in a table?
有谁知道我怎么能看到表中的主键和外键是什么?
EDIT: 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.
编辑:感谢所有的回复。我正在寻找一个SQL查询来做到这一点。现在我正在玩写一个工具,可以列出所有数据库表并显示列。我还要显示哪些键是主键。
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?
我是否必须创建内部联接,以便查看哪些列是主键?
Cheers
5 个解决方案
#1
11
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
Marc
#2
4
see the Querying the SQL Server System Catalog FAQ, How do I find the columns of a primary key for a specified table? and How do I find the columns of a foreign key for a specified table?
请参阅查询SQL Server系统目录常见问题解答,如何查找指定表的主键列?如何找到指定表的外键列?
EDIT: if you want to do it programmatically (which is not clear from your question), that is.
编辑:如果你想以编程方式(从你的问题不清楚),这是。
#3
3
In Management Studio, expand the table and then expand the Columns item. The primary key(s) has a key icon next to them.
在Management Studio中,展开表格,然后展开“列”项。主键旁边有一个键图标。
To see the foreign keys, expand the Constraints item.
要查看外键,请展开“约束”项。
#4
1
You can start with:
你可以从:
SELECT
Table_Name as [TableName],
Column_Name as [ColumnName],
Constraint_Name as [Constraint],
Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY
[TableName],
[ColumnName]
(you can filter then by tableName)
(你可以通过tableName过滤)
#5
1
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 where OBJECT_NAME(parent_object_id) = 'tablename'
If you want to get the foreign key relation of all the tables exclude the where
clause else write your tablename instead of tablename
如果要获取所有表的外键关系,请排除where子句,否则请写入tablename而不是tablename
#1
11
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
Marc
#2
4
see the Querying the SQL Server System Catalog FAQ, How do I find the columns of a primary key for a specified table? and How do I find the columns of a foreign key for a specified table?
请参阅查询SQL Server系统目录常见问题解答,如何查找指定表的主键列?如何找到指定表的外键列?
EDIT: if you want to do it programmatically (which is not clear from your question), that is.
编辑:如果你想以编程方式(从你的问题不清楚),这是。
#3
3
In Management Studio, expand the table and then expand the Columns item. The primary key(s) has a key icon next to them.
在Management Studio中,展开表格,然后展开“列”项。主键旁边有一个键图标。
To see the foreign keys, expand the Constraints item.
要查看外键,请展开“约束”项。
#4
1
You can start with:
你可以从:
SELECT
Table_Name as [TableName],
Column_Name as [ColumnName],
Constraint_Name as [Constraint],
Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY
[TableName],
[ColumnName]
(you can filter then by tableName)
(你可以通过tableName过滤)
#5
1
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 where OBJECT_NAME(parent_object_id) = 'tablename'
If you want to get the foreign key relation of all the tables exclude the where
clause else write your tablename instead of tablename
如果要获取所有表的外键关系,请排除where子句,否则请写入tablename而不是tablename