How to use Oracle linked server shows all tables

Query to List All Tables

Once the linked server is set up, you can use the following query to retrieve all the tables from the Oracle database:

Open your sql management studio, then you can use the sql query below.

SELECT *
FROM OPENQUERY(ORCL_LINK, 'SELECT table_name, owner FROM all_tables ORDER BY owner, table_name')

As a result you will see all schemas and all tables

Show Tables from a Specific Schema

If you want to filter tables by schema (for example, SCHEMA_NAME), you can modify the query like so:

SELECT *
FROM OPENQUERY([YourLinkedServerName], 'SELECT table_name FROM all_tables WHERE owner = ''SCHEMA_NAME''')

Replace SCHEMA_NAME with the specific Oracle schema you're interested in.

Alternatively, if you know the schema you want, you can also query the Oracle tables directly from SQL Server by using:

SELECT * 
FROM [YourLinkedServerName]..SCHEMA_NAME.sys.tables;