CREATE PROC [dbo].[GetOpenQuery_Oracle_Tables](@LinkServ NVARCHAR(100)) AS BEGINDECLARE @SQL NVARCHAR(MAX)='SELECT * FROM OpenQuery('+@LinkServ+', ''SELECT * FROM user_tables ORDER BY TABLE_NAME'')'PRINT @SQLEXEC sp_executesql @SQLENDGO
CREATE PROC [dbo].[GetOpenQuery_Progress_Tables](@LinkServ NVARCHAR(100)) AS BEGINDECLARE @SQL NVARCHAR(MAX)='SELECT * FROM OpenQuery('+@LinkServ+', ''SELECT * FROM sysprogress.SYSTABLES WHERE CREATOR = ''''PUB''''AND ID >= 0 ORDER BY TBL'')'PRINT @SQLEXEC sp_executesql @SQLENDGO
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'