Menu
I know of a statement that I can issue to find out the table with the column I am looking for:
SELECT DISTINCT(table_name)
FROM all_tab_cols
WHERE column_name = 'EMP_ID';
However, how should I modify this so that I may find the table that has both say EMP_ID and EMP_NAME?
831
Compare columns in two tables and list out column names which are different. for ex:- create table t1(c1 number(2), c2 varchar2(10)); create table t2(c1 number(2), c2 varchar2(10)); insert into t1 values(1,'a'); insert into t2 values(1,'b'); result should be column c2 is different.
In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared. For this article, we will be using the Microsoft SQL Server as our database.
You can narrow down your search by providing the owner of the table. The owner is the schema in which the table resides. select owner, table_name from all_tables where table_name='table%' and owner='MYOWNER'; Alternatively you can log into that particular schema and query user_tables.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
Use:
SELECT table_name
FROM all_tab_cols
WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name
HAVING COUNT(DISTINCT column_name) = 2
The count comparison must equal the number of parameters defined in the IN clause.
...but this is a safer means of determining the table in case their are duplicates:
SELECT table_name
FROM all_tab_cols
WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name, owner
HAVING COUNT(column_name) = 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
