Search Column name in Linked Server (Oracle) from Microsoft SQL Server

Question

I need to retrieve Table Names from Linked Oracle Database which is on Linked Server with Microsoft SQL Server.

e.g. I can get table names in SQL Server using:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%TELEPHONE%'

but, I need Oracle Code. I am using Microsoft SQL Server with a linked server to Oracle.

You need Oracle code to do what? You what that query in oracle code? — Stivan, Sep 05 '19 at 13:42
Sorted. Thanks. SELECT * FROM OPENQUERY(ORACLE_DB_NAME,'SELECT table_name, column_name FROM cols WHERE column_name LIKE ''%TELEPHONE%''') — Bionic, Sep 05 '19 at 13:52

Stivan · Accepted Answer · 2019-09-05T13:59:52.393

SELECT 
     * 
FROM 
     OPENQUERY(ORACLE_DB_NAME,'
          SELECT 
               table_name, 
               column_name 
          FROM 
               cols 
          WHERE 
               column_name LIKE ''%TELEPHONE%'''
      )

Based on your comment "Hi, SQL Query to retrieve Oracle Table Names", you can do the following:

SELECT 
     table_name, 
     column_name
FROM 
     cols
WHERE 
     column_name LIKE '%TELEPHONE%';

score 0 · Answer 2 · answered Sep 05 '19 at 13:48

You can use USER_TAB_COLS or ALL_TAB_COLS view for it. Refer oracle documentation for more information on this views

SELECT TABLE_NAME, COLUMN_NAME 
FROM USER_TAB_COLS 
WHERE UPPER(COLUMN_NAME) LIKE '%TELEPHONE%';

UPPER is used in WHERE clause as names are case sensitive if they are created with double quotes and case insesitive if created with no quotes.

Cheers!!

Search Column name in Linked Server (Oracle) from Microsoft SQL Server

2 Answers2