0

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?

Cenderze
  • 1,202
  • 5
  • 33
  • 56
  • What do you mean about only headers? Are you looking for a list of tables, a list of columns for each of those tables, or are you looking for something which selects a few rows from each so you can see example data? – Bridge Jun 20 '17 at 07:46
  • @Bridge With only the headers I mean that when I try to use Exec sp_tables_ex TheSRV I only get the headers correlated to that Query, i.e. `TABLE_CAT`, `TABLE_SCHEM` among Three more, but no data corresponding to the headers. I want to find out a list of tables. – Cenderze Jun 20 '17 at 07:48

1 Answers1

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
Bridge
  • 29,818
  • 9
  • 60
  • 82