April 4, 2006 at 10:49 am
Can someone help me?
I need a command to get the body (text) of all stored procedures?
In other words, I want to say SET @MYVAR = (SELECT All Stored Procedure Code WHERE NAME LIKE 'SOMETHING%')
Thank you!
April 4, 2006 at 11:31 am
select
O.name, S.Text from
sysobjects O
inner
join syscomments S on O.ID = S.ID
--Where O.Name like 'Something'
Order
by O.name, S.colid
April 4, 2006 at 2:13 pm
sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]
Regards,Yelena Varsha
April 4, 2006 at 2:33 pm
Yelena, that will only give the first 256 characters of the proc per line.....
April 4, 2006 at 5:23 pm
Scorpion, thank you very much for your help!
I don't suppose there is a way to format them the way SQL Server displays them when you open a stored procedure? (line breaks, etc.)
April 5, 2006 at 2:42 am
IF you use this approach, remember the little gottcha if you try to run it in SQL Server 2000 environment; the rowsize is 8094 bytes, you can't sort and you cannot get ALL of the SP text in one record (with SPs bigger then 8094 chars) if you do not do some fancy transformations.
Hanslindgren
Correction: SQL Server 2005 has the same behaviour. Which means you have to be careful of big SPs.
April 5, 2006 at 2:53 am
If you retrieve the query text it will contain the linebreaks and linefeeds you need for formatting your SP the way your GUI does. So if you just display the SP text with the help of some decent front end programming language, you will automatically benefit from the inherit T-SQL formatting...
Hanslindgren
April 5, 2006 at 11:33 am
I looked up the solution that Brian Kelly found last year answering to my question on the same topic:
SELECT OBJECT_DEFINITION(<object ID>)
As an example:
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_help'))
I tested his solution and added a comment that we have to change the number of characters that query returns from 256 to whatever number you need if you are returning results in text. See discussion at
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=153610
Regards,Yelena Varsha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply