{"id":108077,"date":"2025-12-29T15:17:00","date_gmt":"2025-12-29T15:17:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108077"},"modified":"2025-12-17T16:53:25","modified_gmt":"2025-12-17T16:53:25","slug":"how-to-view-sql-server-object-code-easily-with-sp_showcode","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/how-to-view-sql-server-object-code-easily-with-sp_showcode\/","title":{"rendered":"How to View SQL Server Object Code Easily with sp_showcode"},"content":{"rendered":"\n

sp_helptext<\/code> has served SQL Server DBAs<\/a> for years, but it has its very obvious – and many – limitations: broken lines, missing triggers, no help with encrypted objects, to name just a few. That’s where sp_showcode<\/code> comes in…<\/p>\n\n\n\n

Why DBAs Need Better Code Retrieval<\/h2>\n\n\n\n

If there’s one thing I did a lot over the years as a SQL Server DBA, it was working with the likes of procedures<\/a> and views<\/a>. Straight away with SQL Server I learned to create procedures and, later, other types of objects, such as functions<\/a> and triggers<\/a> – objects that have code saved in the database.<\/p>\n\n\n\n

It’s very common for those who work with these to frequently deal with the code, whether modifying it or just reading it to understand or confirm some behavior. There are those who prefer to use SQL Server Management Studio<\/a>, navigate the hierarchical structure, right-click and open it. <\/p>\n\n\n\n

And, there are those – like myself – who prefer to display the text directly from the code window with the famous sp_helptext<\/code><\/a>, which I find much faster than using the interface, as demonstrated below:<\/p>\n\n\n\n

Limitations of sp_helptext in SQL Server<\/h2>\n\n\n\n

sp_helptext<\/code><\/a> originally returns the code as one column in multiple lines, whereby each line is a line of the source code. If you copy and paste directly into SSMS, you lose formatting. To bypass this, you can activate text mode in SSMS (shortcut CTRL+T) and voil\u00e0: <\/em>your proc, however complex it may be, returns with the correct formatting and is much easier to copy…in most <\/em>cases!<\/p>\n\n\n\n

This is because, occasionally, the lines may be broken, or in the wrong place, and the column header Text <\/em>comes along. In 99% of cases it’s still very useful, and I think it’s better than looking for it in the interface, but its limitations don’t stop there:<\/p>\n\n\n

\n