Stored procedure is trancated ( first 4000 characters )?

I am using MSSQL.
When I click on stored procedure, HeidiSQL will call

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='xxx' AND ROUTINE_TYPE='FUNCTION' AND ROUTINE_CATALOG='xxx';

However, it will only show the first 4000 characters in the editor.

I would suggest use following statement to get the definition of the routine.

SELECT OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%xxx%'

Then it will get the entire routine.

Am I correct?

That's a good hint. However, MSSQL seems to do things veeery version dependant. I modified quite some logic recently to get the procedure editor running on MSSQL, but everything I do seems to be specific to one or few server versions. So, is your suggestion valid for most other than your server versions as well?

I am not sure. Because I have MSSQL Server 11.0.3000.0 on hand only.

Here is the article on web which suggest to use the above statement:

http://beginsql.wordpress.com/2011/10/01/how-to-find-all-the-stored-procedures-having-a-given-text-in-it/

not working on MSSQL 8, OBJECT_DEFINITION is unknown

On MSSQL 8 I get this for ROUTINE_DEFINITION (that also have the char(4000) limitation) :

ROUTINE_DEFINITION = convert(nvarchar(4000),
(SELECT TOP 1 CASE WHEN encrypted = 1 THEN NULL ELSE com.text END
FROM syscomments com WHERE com.id=o.id AND com.number<=1 AND com.colid = 1))

By the way here is a screen taken side-by-side left is Seven 64bits, right is Windows XP, retrieved information is full of squared characters see screenshot http://i.imgur.com/Lg7yPSK.png

I also have MSSQL 7.0 and trying access stored procedures make HeidiSQL crash with exception message SQL :

Error(208) : Invalid object name 'information_schema.ROUTINES'

i'm using
client :
win7 7601 sp1 x64
HeidiSQL 8.3.0.4694 &

server:
win 7 7601 sp1 x64
SQL Server 2008 r2 10.50.1600.1 x64

and I can't see the whole stored procedures. it is being truncated - only 3510 characters appear
can you help me with this?

It's obviously a limitation of the underlying ROUTINE_DEFINITION column in IS.ROUTINES. This query will show you the truncated data:

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='your_routine_name' AND ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo';

I am thinking I can fix that by casting to NText, but I am unsure. Any suggestions?

http://msdn.microsoft.com/en-us/library/ms188757%28SQL.90%29.aspx says:

ROUTINE_DEFINITION
nvarchar(4000)
Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.
To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

Please check if this one is working for you:

SELECT * FROM sys.sql_modules WHERE object_id=OBJECT_ID('your_routine_name')

Hi.
yes, when i've used sys.sql_modules, it returned the full source.

and so does

exec sp_helptext 'your_routine_name'

Thanks for feedback.

@dodfr: Could you please check which one also works on MSSQL 8. You just said which one is *not* working, and I have no MSSQL 8 for testing out.

MSSQL 7.0 is from 1998, and MSSQL 8 is MSSQL 2000. Is there any chance I get access to some test servers? Probably some public servers on the net, which just throw away written data. I have no clue how to make Heidi more stable for these old MSSQL servers. Everytime I fix something for the newer versions, some user of an older version complains about incompatibility.

@ansgar - I have installed MSSQL 8.0 (2000) on a VM. How can I send you details privately ?

Oh that's nice. You can send anything to my email address listed on the imprint page.

Super. Just tested and committed that "exec helptext" approach, see r4786. Guess you can turn your server off for me now. Although it could be quite helpful for other issues as well. Anyway, thanks so far!

That's great.

thank you very much !

if anyone needs it, I've downloaded MSSQL Server 2000 RTM (8.00.194) evaluation from here:
http://download.microsoft.com/download/SQLSVR2000/Trial/2000/NT45/EN-US/SQLEVAL.exe (~330MB download file)

Very usefull. Just installing on a virtual XP computer.

Hi,

It seems that SQLServer views with a large amount of charcaters are also truncated and are limited to 4000 characters in the editor.
I have a big view with a lot of "union".
Even if it is working when I validate the view, when reading back the content, the body is truncated.

HeidiSQL still uses the following query to get the view body:

EXEC sp_helptext 'dbo.nameofview';

@fuchsyve: can you please verify that query truncates the view body for you, by running it in a query tab?

Hi. It seems that some of the first part of the text from sp_helptext is lost when getting data from MS SQL. I've just recently started using Heidi SQL on MS SQL, so I don't know if it only relates to newer versions of Heidi SQL and/or newer versions of MS SQL.

Example from my SQL Server 2014 database: This is what Heidi SQL 10.2.0.5599 show as routine body:

This is a manual run of sp_helptext:

There might be a logical explanation to this that I'm not aware of.

Yes, this is also reported in issue #667

Thank you - sorry I did not find this issue before posting my comment.