Return columns from one table that doesn't exist on another-CodePudding

I need to verify if some columns from one table are present on anothers.

For example, i have a

table A

x	y
1	2
...	...

and a

table B

w	x
1	2
...	...

The final output should be:

column w

Is this possible?

CodePudding user response：

Use ALL_TAB_COLUMNS and MINUS. Like so:

SELECT column_name
FROM   all_tab_columns 
WHERE  owner = 'whoever owns TABLE_A'
AND    table_name = 'TABLE_A'
MINUS
SELECT column_name
FROM   all_tab_columns 
WHERE  owner = 'whoever owns TABLE_B'
AND    table_name = 'TABLE_B'

This will return the names of any column that exists in TABLE_A but not TABLE_B.

CodePudding user response：

The easiest and straightforward way to check for the column in a table is to use the information schema for column system view. Write a select query for INFORMATION_SCHEMA.COLUMNS as shown below. If the query returns record, then the column is available in the table. USE {{Database Name}} SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{{Table Name}}' AND COLUMN_NAME = '{{Column Name}}'