如何确定哪些列在两个表之间是共享的？

Question

非常新手，对SQL Server不熟悉...我理解连接表的概念，但最简单的确定共享哪些列的方法是什么？

例如，假设我们有表1和表2，表1有100多个列，表2也是如此，但它们只有1个共同的列。

有没有一种简单的方法可以检查是否有任何共享的列，而不必繁琐地逐一进行检查？

这是一个相当琐碎却又非常有用的问题。谢谢

- user2191477

Answer 1

你可以在INFORMATION_SCHEMA表中找到这样的数据，从技术上讲，它们比sys视图更加标准化。 （参见此问题）。以下是您可以使用的查询：

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'等内容。

- Chad

Answer 2

这可能表明存在根本性的设计问题，但要找到两个表共享的列名，有几种选择：

SELECT name 
FROM sys.columns 
WHERE object_id IN (object_id('dbo.Table1'),
                    object_id('dbo.Table2'))
GROUP BY name
HAVING COUNT(*) = 2

或者

SELECT name 
FROM sys.columns 
WHERE object_id = object_id('dbo.Table1')
INTERSECT
SELECT name 
FROM sys.columns 
WHERE object_id = object_id('dbo.Table2')

- Martin Smith

---

我本来想建议在INFORMATION_SCHEMA.COLUMNS上进行自连接（类似于Chad Henderson），但这个方法相当不错。 - Kevin Suchlicki

在尝试比较来自两个数据库的表时，这似乎在SSMS中无法工作。但是对于同一数据库内部，一切都运行良好。 - FreyGeospatial

@FreyGeospatial - 如果所有内容都是三部分命名（包括字符串文字），第二个代码将起作用。SELECT name FROM db1.sys.columns WHERE object_id = object_id('db1.dbo.Table1') INTERSECT SELECT name FROM db2.sys.columns WHERE object_id = object_id('db2.dbo.Table2') - Martin Smith

Answer 3

使用 INFORMATION_SCHEMA.COLUMNS 的方法如下：

像这样：

IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE Table2
GO
CREATE TABLE Table1 (
    a INT
  , b INT
  , c INT
  , d INT
  , e INT
  , f INT
)

CREATE TABLE Table2 (
    c INT
  , d INT
  , e INT
  , f INT
  , g INT
  , h INT
  , i INT
)

GO

SELECT t1.COLUMN_NAME 
FROM        INFORMATION_SCHEMA.COLUMNS AS t1 
INNER JOIN  INFORMATION_SCHEMA.COLUMNS AS t2 ON t1.COLUMN_NAME = t2.COLUMN_NAME 
WHERE t1.TABLE_NAME = 'Table1' AND t2.TABLE_NAME = 'Table2'

--输出

COLUMN_NAME
c
d
e
f

- leoinfo

Answer 4

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Table1'

intersect

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Table2'

- Sagar Narkar

---

欢迎来到 Stack Overflow！虽然这段代码很受欢迎，也许能够提供一些帮助，但如果它包括解释“如何”和“为什么”解决了问题，那将会更好。请记住，你正在回答未来的读者的问题，而不仅仅是现在提问的人！请编辑你的答案，添加解释，并指出适用的限制和假设。 - Toby Speight

INFORMATION_SCHEMA 在 Oracle 中并不存在，这是正确的吗？ - Nikhil S

Answer 5

请使用以下查询（名称用于显示共同列列表）：

select name from syscolumns s1 where id = object_id('table1') and            exists(select 1 from syscolumns s2 where s2.name = s1.name and s2.id = object_id('table2'))

- yogeshwar gutte

Answer 6

要确定是否有相似的列可能比其他解决方案建议的更棘手。我们可能认为两个列是相同的，因为它们具有相同的名称，但实际上，在使用多个人创建、删除和/或更改数据结构的大型数据库中，不一致性可能会发生。

我们检查的参数越多，就越有信心我们的列是相似的，而无需手动检查原始数据。

1. 首先，我建议您运行一个查询来了解给定列的参数。

SELECT 
    *
FROM 
    DATABASENAME.INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME = N'TABLE1'

这将返回表中列的多个元数据列。我发现一些有趣的唯一性元数据包括...

2. 在我的情况下，我已经确定了列属性COLUMN_NAME，IS_NULLABLE和DATA_TYPE，以确定我的列是否真正匹配。

SELECT 
    DISTINCT A.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
    LEFT join INFORMATION_SCHEMA.COLUMNS B 
        ON A.COLUMN_NAME = B.COLUMN_NAME 
        AND A.DATA_TYPE = B.DATA_TYPE
        AND A.IS_NULLABLE = B.IS_NULLABLE
WHERE 
    A.TABLE_NAME = N'TABLE1'
    AND B.TABLE_NAME = N'TABLE2'

3. 概念检查...也许当我们仅使用COLUMN_NAME进行JOIN时，有10个匹配列。也许当我们使用COLUMN_NAME AND DATA_TYPE进行JOIN时，有7个匹配列。也许当我们像上面的示例一样使用所有三个条件时，有4个匹配列。这是否意味着您只能在4个匹配列上进行JOIN...绝对不是。它意味着您需要考虑如何根据您打算JOIN的表来制定错误处理和转换。重点是要小心在INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME上执行JOIN，因为您的结果可能与预期相差很远。

- ѺȐeallү

Answer 7

这里有一个方便的查询语句，可以用来列出表中的列：

SELECT c.name ColumnName
FROM sys.columns c INNER JOIN
     sys.tables t ON c.object_id = t.object_id 
WHERE t.name = 'something'

这里是一个JOIN，你可以用它来找到共同的列名：

SELECT * 
FROM  (SELECT c.name ColumnName
        FROM sys.columns c INNER JOIN
             sys.tables t ON c.object_id = t.object_id 
        WHERE t.name = 'table1'
      )t1
JOIN (SELECT c.name ColumnName
        FROM sys.columns c INNER JOIN
             sys.tables t ON c.object_id = t.object_id 
        WHERE t.name = 'table2'
     )t2
ON t1.ColumnName = t2.ColumnName

- Hart CO

Answer 8

IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2


SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
INTO        #Table1
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       TABLE_NAME = 'TABLE_NAME1'
ORDER BY    TableName
            ,ColumnName;

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
INTO        #Table2
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       TABLE_NAME = 'TABLE_NAME2'
ORDER BY    TableName
            ,ColumnName;

SELECT #Table1.ColumnName
FROM #Table1
JOIN #Table2 ON #Table1.ColumnName = #Table2.ColumnName

- jadki