Sql Server 2012 Sp_Helptext Extra Lines Issue

SQL server 2012 SP_HELPTEXT extra lines issue

I can replicate this behaviour if I run sp_helptext with Results to grid set, then copy and paste the results from grid into a new query or any other text editor.

This seems to be a change in the behaviour of sp_helptext from previous editions, since this effect isn't displayed with standard grid result sets.

The simplest work-around will be to run sp_helptext with Results to text set (Query -> Results to > Results to text, shortcut CTRL + T.

You may need to increase the maximum number of characters per line in Results to text to get the output you expect - Tools > Options > Query Results > Results to text - set "maximum number of characters displayed in each column" to the maximum value of 8192.

Why does sp_helptext in SQL Server return different results?

sp_helptext has some intentional quirks built-in, and some "unexpected" behaviors.

I put that in quotes because most people who use it haven't actually read the documentation, let alone gone into the system procedures and looked at the actual definition of the procedure itself.

I tend to use the definitions stored in the system tables instead:

SELECT definition
FROM sys.all_sql_modules S 
WHERE OBJECT_NAME(S.object_id) = 'MySProc'
;

This typically gives me more consistent results in some regards, and also allows me to do immediate alterations if need be. Using sp_helptext a lot of people store the output in a variable and then modify the variable value; with a SELECT straight from sys.all_sql_modules, you can just perform desired modifications directly on the returned value without any variable operations.

(By the way, this same query here works on sp_helptext itself, and you're welcome to look at its definition to try to understand what it's doing and what's happening to your definitions. Just make sure you don't accidentally ALTER it...)

I know this doesn't exactly answer your question of "Why is this happening?", and I hope that someone else's response will help you with that. In the meantime, you can try taking a look at the system tables and see if maybe they will provide you with a working solution.

Good luck!

First Edit:
If you're looking to find dependencies, depending on your version of SQL you can use this:

SELECT 
    ISNULL(D.referenced_server_name,@@SERVERNAME) + '.' + ISNULL(D.referenced_database_name,DB_NAME()) + '.' + D.Referenced_Entity_Name AS [Referenced Tables]
FROM sys.procedures P
INNER JOIN sys.sql_expression_dependencies D
    ON P.object_id = D.referencing_id
WHERE P.name = 'MySProc'
;

Returns a concatenated string:

[Referenced Tables]
--------------------
ServerName\InstanceName.DBName.TableName

SQL Server stored procedure line number issue

It's the 9th line from the CREATE PROCEDURE statement. A SQL statement is often multiline so "line 9" will refer to the first line of the statement (eg INSERT or UPDATE)

However, if you have comments above the CREATE PROCEDURE or blank lines before it then you can't rely on this... so run ALTER PROC with ALTER PROC as first line in the batch.

Using sp_helptext in classic asp

The problem is you are only displaying the first line of the sp_HelpText output. SQL Server returns the output as a single column recordset containing a column called [Text].

This means you need to iterate through the rows to display the rest of the output.

Using your first example;

<% 
' .......
sql = "exec sp_helptext 'some_ProcName_Here'"
objRs.open sql, objConn
Do While Not objRs.EOF
  Text=objRs("Text")
  response.write(Text)
  objRS.MoveNext
Loop
%>

This isn't ideal but will work, from experience (especially with more complex stored procedures) I find something like this is better in the long run;

Dim sql, cmd, rs, data

Set cmd = Server.CreateObject("ADODB.Command")
sql = "sp_HelpText"
With cmd
  'Use your connection string instead of instantiating an ADODB.Connection object.
  .ActiveConnection = conn_string
  .CommandType = adCmdStoredProc
  .CommandText = sql
  .Parameters.Append(.CreateParameter("@objname", adVarWChar, adParamInput, 776))
  .Parameters.Append(.CreateParameter("@columnname", adVarWChar, adParamInput, 128))
  Set rs = .Execute(, Array("some_ProcName_Here"))
  If Not rs.EOF Then data = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

This method gives you a 2-Dimensional Array containing the row data in the data variable. You can then use standard Array techniques to manipulate the output.

Dim output, row, rows

If IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0 To rows
    output = output & "<br />" & data(0, row)
  Next
  Call Response.Write(output)
End If

---

Links

• Using METADATA to Import DLL Constants - If your having trouble with the ADO constants (adCmdStoredProc etc.) this will fix it for you.

How can I remove empty lines in SQL Server Management Studio (SSMS)?

It is not built in. The find and replace can be used with regex's and someone crafty may have a solution for that.

Why is SSMS altering my stored procedures (re-formatting, changing exec to EXECUTE, etc.)

This happens when you are working with Always Encrypted and have the option "Enable Parameterization for Alway Encrypted" enabled.

To fix:

1. Go to: Query > Query Options > Execution > Advanced
2. un-check Enable Parameterization for Alway Encrypted (its at the end of the list)

You should only have this option enabled when you are working with ad-hoc queries and not stored procedures.

Replace a newline in TSQL

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

Incorrect syntax near ',' for audit trigger in SQL Server

The error is raised because your trigger has dynamic sql and at least one of the variable is not being set prior to be included in the dynamic sql. This will end up with an invalid sql statement. If you want to see the dynamic sql you could add a print or select statement to output the contents of @SQL before you execute it. You would have to use SSMS to view this but it would help with debugging.

Execute sp_helptext from keyboard shortcut on Visual Studio

This Visual Studio Extension is the one you are looking for.

Added some extra commands for Visual Studio SQL editor under the menu "SQL", so you can do the same things as with SQL Server Management Studio. For example, select the table name, then press the shortcut key, it will run "exec sp_help tableName".

• To run sp_help, press "Alt + 2";
• To run sp_helpText, press "Alt+ 3";
• To search for tables by the table name, press "Alt+ 4";
• To search for tables by the column name, press "Alt+ 5";
• To search for dependencies, press "Alt+ 6";
• To search for stored procedures, press "Alt+ 7";
• To run "Select * from table", press "Alt+ 0";

Crollan's SQL Server Management Studio addIn - Crollan SQL Auto Complete, has lots of other powerful features. One of them is to automatically find out the potential ways to join tables. The addIn can be found on

https://visualstudiogallery.msdn.microsoft.com/bf161b31-0826-4f07-905b-d5d09c1227bb or

Crollan.com

---

Related Topics