如何确定两个表之间共享哪些列? [英] How to determine which columns are shared between two tables?

问题描述

这里是SQL Sever的新手...我了解联接表等的概念，但是确定共享哪些列的最简单方法是什么?

Very new to SQL Sever here... I understand the concept of joining tables, etc. but what is the easiest way to determine which columns are shared?

比如说我们有表1和表2，假设表1和表2一样有100列，但是它们只有1列相同.

Say for instance we have Table 1 and Table 2, assume table 1 has over 100 columns as does table 2, but they only have 1 column in common.

是否有一种简单的方法来查看哪一列/是否共享了，而不会烦人地检查呢?

Is there a simple way to check to see what column / if any are shared without annoyingly going in and checking?

相当琐碎的问题，但非常有用.谢谢

Pretty trivial question but very useful. Thanks

推荐答案

您可以在INFORMATION_SCHEMA表中找到类似的数据.从技术上讲，这些视图比sys视图更为标准化. (请参见此问题.)

You can find data like this in the INFORMATION_SCHEMA tables. Technically those are more standardized than the sys views. (See this question.)

这是您可以使用的查询:

Here's a query you could use:

select A.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS A
join INFORMATION_SCHEMA.COLUMNS B
  on A.COLUMN_NAME = B.COLUMN_NAME
where A.TABLE_NAME = 'table1'
  and B.TABLE_NAME = 'table2'

如果需要指定架构以避免名称冲突，请在where子句中添加A.TABLE_SCHEMA = 'dbo'等.

If you need to specify the schema to avoid name collisions, add A.TABLE_SCHEMA = 'dbo' etc to the where clause.

这篇关于如何确定两个表之间共享哪些列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！