How to use system SP result set in a T-SQL script 1

[NeotheOne]

Hi,
Here is my problem.
I need to read the tables list from a linked server database in order to copy the content of some table, one by one, but I don't know how to fetch the SP_TABLES_EX result set to do it.
Someone could help me ?

Thanks

 

[JohnDTampaBay]

You'll need to build a table with a structure that will accept the output of sp_Tables_Ex and then use INSERT..EXEC to capture the sp results:

CREATE TABLES #Stuff (
  TABLE_CAT    sysname    NOT NULL
  , TABLE_SCHEM  sysname  NOT NULL
  , TABLE_NAME   sysname  NOT NULL
  , TABLE_TYPE   sysname  NOT NULL
  , REMARKS      sysname  NULL
)

IINSERT #Stuff (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS)
EXEC sp_Tables_Ex LinkedServer

SELECT * FROM #Stuff

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.