如何从多个表中获得公共列
本文关键字： | 更新日期: 2023-09-27 18:13:33
在我的一个应用程序中，我需要获得表的公共列。假设从NorthWind数据库我有两个表Order和OrderDetail，需要得到这些表的公共列，如:OrderID。有任何sp或机制或脚本谁可以为我做这件事。如果有任何想法，请与我分享。如有任何疑问请咨询，谢谢
这对我来说很有用，可以找到同名的列。
select a.column_name
from information_schema.columns a
join information_schema.columns b on a.column_name = b.column_name and b.table_name = 'table1'
where a.table_name = 'table2'
要获取订单的FK信息，请使用:
DECLARE @tab int
SELECT @tab = object_id FROM sys.tables WHERE NAME = 'order'
SELECT t.name as [Table], fkc.constraint_column_id, c.name as [Column]
FROM sys.foreign_key_columns as fkc
INNER JOIN sys.tables as t on fkc.parent_object_id = t.object_id
INNER JOIN sys.columns as c on fkc.parent_object_id = c.object_id and fkc.parent_column_id = c.column_id
WHERE fkc.referenced_object_id = @tab