Solved

SQL OBJECT_DEFINITION returns stored procedure text for editing

Posted on 2016-07-26
9
51 Views
Last Modified: 2016-07-27
I've been using OBJECT_DEFINITION for a long time now, and it has been outputting text of stored procedures to the results grid in a nice and readable format (just like sp_helptext).

Yesterday, I upgraded my machine to Windows 10, and had to reinstall SQL Server (management studio). Now all of a sudden OBJECT_DEFINITION is outputting the entire store procedure text in one line, completely unreadable. How do I make it continue to return text to the results grid in a readable format like sp_helptext.

Extra Details
I have a stored procedure I wrote called "sp_prepareSP" which uses OBJECT_DEFINITION to retrieve the stored procedure text. It then appends text before and after the returned text. Things like "IF EXISTS(...) DROP PROCEDURE", and other things like at the end "GRANT EXECUTE ON ...".
0
Comment
Question by:pzozulka
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 41730489
It works for me. What version of SSMS you have installed?
0
 
LVL 8

Author Comment

by:pzozulka
ID: 41730510
2016. Had 2014 before reinstalled ssms.
0
 
LVL 25

Expert Comment

by:chaau
ID: 41730517
Do you use "results to grid" or "results to text"?
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 8

Author Comment

by:pzozulka
ID: 41730526
Only results to grid. I need this to work using grid.
0
 
LVL 25

Expert Comment

by:chaau
ID: 41730547
I think it still should be fine. What happens when you select it and then paste it to a text editor? The difference between the OBJECT_DEFINITION and sp_helptext is that the former returns a text blob in a single row, and the latter is returns a table with each line in a row:
ssmsI have got ssms2014 - they release new products too fast for me to test them all
0
 
LVL 8

Author Comment

by:pzozulka
ID: 41730548
When I paste it into the query window it shows up as a single line.
0
 
LVL 25

Expert Comment

by:chaau
ID: 41730556
It must be a bug in ssms2016 then.
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 41730560
No, it is not a bug. It is a feature.
Check this article out. They now have a setting "Retain CR/LF on copy or save". Try it:
CRLF
0
 
LVL 8

Author Comment

by:pzozulka
ID: 41731783
That worked. Thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using this function 4 38
WSUS Console loosed connectivity to the database. 13 35
Want an individual results display div 8 40
Delete old Sharepoint backups 2 11
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question