Get Open Query Linked Server Tables

This set of Stored Procedures will enable you to view the available tables for Oracle and Progress databases linked to SQL Server.

A Simple set of Stored Procedures that return a list of Tables from Oracle or Progress databases linked into SQL Server. The SELECT statement can also be used independently on the relevant application.

Oracle

CREATE PROC [dbo].[GetOpenQuery_Oracle_Tables](@LinkServ
NVARCHAR(100)) AS BEGIN
DECLARE @SQL NVARCHAR(MAX)='
SELECT * FROM OpenQuery('+@LinkServ+', ''SELECT * FROM user_tables ORDER BY TABLE_NAME'')'
PRINT @SQL
EXEC sp_executesql @SQL
END
GO

Progress

CREATE PROC [dbo].[GetOpenQuery_Progress_Tables](@LinkServ NVARCHAR(100)) AS BEGIN
DECLARE @SQL NVARCHAR(MAX)='
SELECT * FROM OpenQuery('+@LinkServ+', ''SELECT * FROM sysprogress.SYSTABLES WHERE CREATOR =
''''PUB''''
AND ID >= 0 ORDER BY TBL'')'
PRINT @SQL
EXEC sp_executesql @SQL
END
GO

To run them simply execute the appropriate Stored Procedure with the linked server name.

EXEC GetOpenQuery_Oracle_Tables 'Server_Name'

EXEC GetOpenQuery_Progress_Tables 'Server_Name'