Stored procedure is trancated ( first 4000 characters )?

stephen.lai posted 4 years ago in General
I am using MSSQL.
When I click on stored procedure, HeidiSQL will call


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.

FROM sys.procedures

Then it will get the entire routine.

Am I correct?
ansgar posted 4 years ago
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?
stephen.lai posted 4 years ago
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:

dodfr posted 4 years ago
not working on MSSQL 8, OBJECT_DEFINITION is unknown
dodfr posted 4 years ago
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))
dodfr posted 4 years ago
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'
itsho posted 3 years ago
i'm using
client :
win7 7601 sp1 x64
HeidiSQL &

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 unhappy
can you help me with this?
ansgar posted 3 years ago
It's obviously a limitation of the underlying ROUTINE_DEFINITION column in IS.ROUTINES. This query will show you the truncated data:

I am thinking I can fix that by casting to NText, but I am unsure. Any suggestions?
ansgar posted 3 years ago
http://msdn.microsoft.com/en-us/library/ms188757%28SQL.90%29.aspx says:

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')
itsho posted 3 years ago
yes, when i've used sys.sql_modules, it returned the full source. smile
itsho posted 3 years ago
and so does
exec sp_helptext 'your_routine_name'
ansgar posted 3 years ago
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.
ansgar posted 3 years ago
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.
itsho posted 3 years ago
@ansgar - I have installed MSSQL 8.0 (2000) on a VM. How can I send you details privately ?
ansgar posted 3 years ago
Oh that's nice. You can send anything to my email address listed on the imprint page.

ansgar posted 3 years ago
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!
itsho posted 3 years ago
That's great. smile

thank you very much !
itsho posted 3 years ago
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)
ansgar posted 3 years ago
Very usefull. Just installing on a virtual XP computer.
fuchsyve posted 2 years ago

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.

ansgar posted 2 years ago
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?

Please login to leave a reply, or register at first.