title: Get the text of a stored procedure in SQL Server
body: <p>I am trying to save an old stored procedure into a string. When I use the following I do not get any line breaks.</p>
  
  <pre><code>SELECT @OldProcedure = Object_definition(object_id)
  FROM   sys.procedures
  WHERE  name = @ProcedureName
  </code></pre>
  
  <p>Any suggestions on how to get the stored procedure text with the line breaks?</p>
  
  <p>I was thinking of using <code>sp_helptext</code></p>
  
  <p>Thanks</p>
  
  <p><strong>Update</strong></p>
  
  <p>I am copying and pasting the results from the results which will show me a single line.</p>
  
  <p>As for the script as I am storing the results in a db field.  I am create a tool that will generate procedures on the fly, but I wanted to create a history of them.</p>
  
  <p><strong>Update</strong></p>
  
  <p>Turned out the Object_definition does what I want,  But for some reason when I copy it from the results I get a single line.</p>
  
  <pre><code>Declare @sql varchar(max) ;
  SELECT @sql=Object_definition(object_id)
  FROM   sys.procedures
  WHERE  name = 'Test_QueryBuilder';
  
  drop procedure Test_QueryBuilder
  exec( @sql)
  print @sql
  </code></pre>