title: Nested loops in SQL Server, with results from stored procedures feeding nested stored procedures
body: <p>I've worked with databases for a while but haven't ever needed to do something like this.</p>
  
  <p>Background - I've plugged SQL Server into the back of several apps via linked server. There's no documentation about the db-insides of these applications. </p>
  
  <p>I'd like to record meta-data about each linked server. The approach I was thinking is something like this:</p>
  
  <p>(note <code>sp_columns</code> doesn't work on these linked servers)</p>
  
  <p><b> Step 1 </b></p>
  
  <p>sp_linkedservers (to get list of linked servers using some criteria)</p>
  
  <p>-> sp_tables_ex (to get list of tables)</p>
  
  <p>--> create temp tables based on table names (insert into ...)</p>
  
  <p><b> Step 2 </b></p>
  
  <p>loop through temp tables, and for each table</p>
  
  <p>--> sp_columns and write output to final destination table which will record linked server, table, column, type...</p>
  
  <p>This pseudo-system table can can searched when looking for meta-data about the linked servers columns.</p>
  
  <p>What is the SQL Server way of approaching this problem?</p>
  
  <p><strong>EDIT</strong>: I should add the linked servers are Firebird and Progress databases accessed with ODBC connections.</p>