exec sp_tables_ex returns nothing on a server I can find using exec sp_linkedservers

Question

I'm trying to find data in a database which hasn't been used that much.

I've seen that it is a linked server using:

exec sp_linkedservers

which returns the servername, TheSRV along with for instance its catalog name S100.

I then try to find information about which tables are present in the linked server, TheSRV.

For this I try to use:

exec sp_tables_ex TheSRV

but this returns only the headers, without any data.

I can do a Query using openquery like this:

Select name, date From OPENQUERY(TheSRV, 'Select Name, Date from S100.F1.TableName')

which returns valid data.

How can I find the information I need about tables present, when I can't find a list of tables?

Answer 1

You should be able to use one of the standard ways of listing schema objects, qualifying server name as part of the four part naming convention:

SELECT *
FROM   TheSRV.S100.INFORMATION_SCHEMA.TABLES T
WHERE  T.TABLE_TYPE = 'BASE TABLE'

To see the columns in each of those tables:

SELECT C.*
FROM   TheSRV.S100.INFORMATION_SCHEMA.TABLES T
       INNER JOIN TheSRV.S100.INFORMATION_SCHEMA.COLUMNS C
               ON T.TABLE_NAME = C.TABLE_NAME
                  AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE  T.TABLE_TYPE = 'BASE TABLE'
ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION