0

We have an internal resource used by our developers and support to help identify data issues when we are diagnosing problems in our system. It's used to support a collection of data feeds and they are very customized. What we would like to do is allow one of our users to click the name of a stored procedure and then view the CREATE script of that stored procedure. This is done on an ASP.NET form.

I can get this information by using this statement:

select object_definition(object_id(routine_name)) 
from information_schema.routines 
where routine_name like '%sprocname%'

However, the returned script is unformatted and in a single cell, on a single line. Is there a way for me to format this as if I had right-clicked on the stored procedure in SSMS and select "Script stored procedure as CREATE to"? Because it is all on a single line, if I have commented any portion of the procedure out using two dashes it comments the entire rest of the procedure that follows it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LarryG
  • 627
  • 1
  • 6
  • 14
  • 1
    Well, you shouldn't be returning it to a "cell" you should be returning results to text. And use `SELECT name, OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE name LIKE ...;` – Aaron Bertrand Jan 10 '22 at 19:19
  • 1
    While SSMS does have an option these days to `Retain CR/LF on copy or save` I find results to text much easier to work with (and much less manual copy/paste work if there are a large number of results). – Aaron Bertrand Jan 10 '22 at 19:20
  • 1
    As Aaron mentioned, it's SSMS removing the line breaks. Fix by changing option, see steps here: https://stackoverflow.com/questions/2679481/ssms-results-to-grid-crlf-not-preserved-in-copy-paste-any-better-techniques – Stephan Feb 08 '22 at 05:49
  • 1
    Does this answer your question? [SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques?](https://stackoverflow.com/questions/2679481/ssms-results-to-grid-crlf-not-preserved-in-copy-paste-any-better-techniques) – Stephan Feb 08 '22 at 05:50

0 Answers0