title: SQL Server recompile marked stored procedures
body: <p>With the following script, I've tried to recompile all our stored procedures on the database because we're upgrading to SQL Server 2012 (from 2005) and I want to make sure beforehand, that they don't break.</p>
  
  <pre><code>DECLARE @schema VARCHAR(20),
          @spName VARCHAR(MAX),
          @fullName VARCHAR(MAX)
  
  DECLARE storedProcedureCursor CURSOR FOR
      SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME
      FROM ASZ_GWZ.information_schema.routines 
      WHERE routine_type = 'PROCEDURE'
  
  OPEN storedProcedureCursor
  FETCH NEXT FROM storedProcedureCursor INTO @schema, @spName
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @fullName = @schema + '.' + @spName
      EXEC sp_recompile @objname = @fullName
  
      FETCH NEXT FROM storedProcedureCursor INTO @schema, @spName
  END 
  
  CLOSE storedProcedureCursor
  DEALLOCATE storedProcedureCursor
  </code></pre>
  
  <p>Unfortunately, the result was a little bit different from what I expected, since the stored procedures were only <strong>marked</strong> for recompilation and didn't actually get recompiled.</p>
  
  <blockquote>
    <p>Object 'dbo.xyz' was successfully marked for recompilation.</p>
  </blockquote>
  
  <p>How can I really recompile all (or all now for recompilation marked) stored procedures?</p>
  
  <p>Thanks in advance</p>