March 20, 2009 at 9:11 pm
Hi everyone,
I have a query to retrieve the text of stored procedure in the database base on system view Information_schema.routines
select routine_name , routine_definition
from information_schema.routines
where routine_type = 'procedure' and routine_definition like '%INSERT%INTO%'
But , i noticed that if my stored procedure has so many character, its content will not show fully, it is truncated.
So, is there any way to retrieve all character of stored procedure?
Thanks in advanced 🙂
sol
March 21, 2009 at 2:32 am
Hi,
routine_definition is returns with only nvarchar(4000)
so try
exec sp_helptext 'SP'
ARUN SAS
March 21, 2009 at 2:45 am
Hello,
To get the full definition for multiple SPs you could use the sys.sql_modules view.
So something like:-
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
Where o.type = 'P'
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 21, 2009 at 3:04 am
To ARUN SAS,
Although the sp_helptext return full length of sp, but it's just for one sp 🙂
To John Marsh,
Yeah, the sys.sql_modules help me very much,
thanks all of you
While i ask you guys, i also find out one system stored which have the same result as John said, but with a little harder to get full result
here it is
SELECT distinct s.id, s2.[name]
FROM sys.syscomments s
INNER JOIN sys.sysobjects s2
ON s.id = s2.id
WHERE s2.xtype = 'p' AND s.[text] LIKE '%insert%into%'
ORDER BY s.id
sol
March 21, 2009 at 12:34 pm
sol (3/21/2009)
While i ask you guys, i also find out one system stored which have the same result as John said, but with a little harder to get full resulthere it is
SELECT distinct s.id, s2.[name]
FROM sys.syscomments s
INNER JOIN sys.sysobjects s2
ON s.id = s2.id
WHERE s2.xtype = 'p' AND s.[text] LIKE '%insert%into%'
ORDER BY s.id
sol
Hi sol
syscomments is marked as deprecated in SQL Server 2005/2008. It will be removed in later versions of SQL Server.
Greets
Flo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply