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'

Author

Gavin Clayton

I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.