How to determine which columns are split between two tables?

Question

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 split?

Say, for example, that we have table 1 and table 2, suppose table 1 has more than 100 columns, like table 2, but they only have 1 column.

Is there an easy way to check which column / if there is one, without annoying transition and validation?

Pretty trivial question, but very useful. thanks

Answer 1

Data can be found in the INFORMATION_SCHEMA tables. Technically, they are more standardized than sys representations. (See this question .)

Here you can use the query:

 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' 

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

Answer 2

Use INFORMATION_SCHEMA.COLUMNS as follows:

 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' 

- OUTPUT

 COLUMN_NAME c d e f 

Answer 3

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

Answer 4

This may indicate a fundamental design problem, but to search for column names shared by both tables, several parameters would be

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

or

 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') 

Answer 5

Here is a handy query that you can use to display columns in a table:

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

And here is the JOIN that you can use to search for common column names:

 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 

Answer 6

To know if you have similar columns can potentially be more complicated than other solutions. We might think that the two columns are the same because they have the same name, but in fact, when you are working in a large database with more than one person creating, deleting and / or modifying a data structure inconsistency, it may to happen.

The more parameters we check for similarity, the more confident we can be that our columns are similar without manual control of raw data.

1. First, I suggest you run a query to understand the parameters of this column.

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

This will return multiple metadata columns in the columns of the table. Some metadata that I found interesting for uniqueness included ...

2. In my case, I defined the column attributes COLUMN_NAME, IS_NULLABLE, AND DATA_TYPE to determine if my columns really match.

 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. The concept of verification ... Maybe if we JOIN , using only COLUMN_NAME , 10 corresponding columns will be found. Perhaps when we JOIN , using COLUMN_NAME AND DATA_TYPE , there are 7 corresponding columns. Perhaps when we use all three conditions, as in the example above, there are 4 corresponding columns. Does this mean that you can only join 4 corresponding columns ... absolutely not. What this means is that you will need to consider how to handle errors and casting depending on how you intend to connect to the tables. The point is the careful execution of the JOIN on INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME , since your results may be far from the intended.

Answer 7

Use the following query (the name used to display the list of coman columns)

 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'))