title: Programmatically replacing linked server references with local database references in SQL Server stored procs?
body: <p>As a part of a scripted procedure I'm trying to <em>programmatically</em> update references to linked servers in stored procs. We have several references like this:-</p>
  
  <pre><code>SELECT foo, bar
  FROM [Server].[Database].dbo.[Table]
  </code></pre>
  
  <p>Which I would like to translate to:-</p>
  
  <pre><code>SELECT foo, bar
  FROM [Database].dbo.[Table]
  </code></pre>
  
  <p>I would like to do this entirely programmatically in a 'fire and forget' script across several databases.</p>
  
  <p>The idea I have right now is to use metadata to find references to linked tables, read the text of each sp from metadata again, adjust each sp's text, then shove each block of updated text into an exec statement to rebuild 'em one-by-one.</p>
  
  <p>I do wonder whether this will be a humongous pain however, so does anybody have any better ideas? I am open to using powershell if that could provide a better solution.</p>
  
  <p>Thanks in advance!</p>