Holen Sie sich Open Query Linked Server-Tabellenspaltennamen

Mit diesem Satz gespeicherter Prozeduren können Sie die verfügbaren Spalten in einer Tabelle für mit SQL Server verknüpfte Oracle- und Progress-Datenbanken anzeigen.

A Simple set of Stored Procedures that return a list of Columns from Oracle or Progress databases linked into SQL Server, using dynamic SQL.

The SELECT statement can also be used independently on the relevant application.

Oracle

CREATE PROC [dbo].[GetOpenQuery_Oracle_Columns](@LinkServ NVARCHAR(100),@LinkTable NVARCHAR(100)) AS BEGINDECLARE @SQL NVARCHAR(MAX)='SELECT * FROM OpenQuery('+@LinkServ+', ''select * from DBA_TAB_COLUMNS where OWNER = ''''LIVE''''AND table_name= '''''+@LinkTable+''''''')'PRINT @SQLEXEC sp_executesql @SQLENDGO

Progress

ALTER PROC [dbo].[GetOpenQuery_Progress_Columns](@LinkServ NVARCHAR(100),@LinkTable NVARCHAR(100)) AS BEGINDECLARE @SQL NVARCHAR(MAX)='SELECT * FROM OpenQuery('+@LinkServ+', ''select * from sysprogress.SYSCOLUMNS_FULL where TBL = '''''+@LinkTable+''''''')'PRINT @SQLEXEC sp_executesql @SQLENDGO

Running the code

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

EXEC GetOpenQuery_Oracle_Columns 'Server_Name','Table_Name'EXEC GetOpenQuery_Progress_Columns 'Server_Name','Table_Name'