CODEWITHSUNDEEP
sqlsql-server-2008
Liyakathali
Liyakathali

Reputation: 71

Cannot select only columns with same column name from different table

I have two tables named a, and b.

Table a contains 50 columns, and table b contain 75 columns

There are same column name in this two tables. How to find the same columns?

Upvotes: 1

Views: 256

Answers (3)

Jorge E. Hernández
Jorge E. Hernández

Reputation: 2938

You can also query for the view

INFORMATION_SCHEMA.COLUMNS

This way:

SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name IN ('your_a_table','your_b_table')
ORDER BY column_name

With this you can get a nice view of all the columns in common (and other ones too) between the two tables.

Upvotes: 1

Husen
Husen

Reputation: 1587

Please try this it will help you

select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='a'
INTERSECT
select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='b'

Upvotes: 4

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can query the sys.columns and then do an INTERSECT to find the common column names:

SELECT name
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'a'

INTERSECT 

SELECT name
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'b'

Upvotes: 6

Related Questions