Small, but useful SSMS tip

In my recent project I ran into situation, where I had to turn off page compression on some of the indexes. Unfortunately, indexes were dropped and re-created in stored procedures, so I had to identify those procedures and change the data_compression setting from PAGE to NONE.
To do this I’ve decided to query sys.sql_modules DMV and check the definition column, which contains stored procedure text. I just copied text into clipboard and then pasted into new query windows and … no voilà effect  :-(. The stored procedure text wasn’t formatted as I expected, it was formatted as a one line. The reason of this behaviour is improper handling of CR/LF characters by SSMS by default.

I have used AdventureWorks2017 database and uspGetBillOfMaterials procedure for demonstration. Here is the uspGetBillOfMaterials, scripted and properly formatted by SSMS:

And here is the alternative way of getting text of the stored procedure from sys.sql_modules DMV:

The main problem with this formatting is when your procedure consists from many lines of code and some of the lines are commented out, as it was in my case. In situation like this, every T-SQL command between two hyphens (–) characters and newline character will be treated as a comment, as you can see on pictures below:

I started browsing internet for quick solution and I have found couple of suggestions, but none of them was elegant. Luckily, then I have found a tip on dba.stackexchange.com forum from Przemyslaw Remin (https://www.linkedin.com/in/przemyslaw-remin/).

All you need is to change default setting in SSMS – go to Tools->Options->Query Results->SQL Server->Results to grid and check Retain CR/LF on copy or save setting:

Then restart the SSMS, copy and paste query text from the DMV and … voilà!

You can copy more procedure texts at once, paste it in one query window, and make other changes you need or save it for later. I hope some of you will find this tip useful.

Thanks for reading