distal-attribute
distal-attribute
distal-attribute
distal-attribute

Stored procedure is trancated ( first 4000 characters )?

User, date Message
Written by stephen.lai
1 year ago
Category: General
2 posts since Tue, 09 Apr 13
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?
Written by ansgar
1 year ago
4801 posts since Fri, 07 Apr 06
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?
Written by stephen.lai
1 year ago
2 posts since Tue, 09 Apr 13
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/
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
not working on MSSQL 8, OBJECT_DEFINITION is unknown
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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))
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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'
 

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