Search code examples
sqlsql-server-2008db2openquery

Find all tables of a linked server using server provider IBMDASQL from SQL Server

I have some linked servers which, when I make the statement, 

exec sp_linkedservers

in SQL server shows up as:

SRV_NAME      SRV_PROVIDERNAME         SRV_CAT
SERVER1       IBMDASQL                 INICAT1
SERVER2       IBMDASQL                 INICAT1

I've been able to access another linked server with the same provider name using:

SELECT * FROM OPENQUERY(SERVER3,'SELECT * FROM INICAT1.Schema3.Table')

I don't know the specifics of the two other servers, only that they have the same value on SRV_CAT.

Does anyone know how one can find table information from a server using IBMDASQL as serverprovider?

What I've tried is to use the "regular" ways of finding the information, i.e. for instance using:

exec sp_tables_ex SERVER1

but this only shows an empty result set.

Solution

  • Using openquery() one can use:

    SELECT * FROM OPENQUERY(LINKED_SRV,
'SELECT * FROM SYSTEM.TABLES WHERE Table_Schema LIKE ''TableofInterest''
 order by Table_Name')