Get stored procedure code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get stored procedure code

Author

Topic

asafg
Starting Member

39 Posts

Posted - 2008-12-08 : 07:54:26

Hi,

I'm building a .net application that help me query the DB structure.
(I know I can do all in the management studio but...)

I need to be able to get the text of the code of a stored procedure (such as mySP.sql)

1.Is there a query that gets this text for me?
E.G:
*Query:
select text from someTable where SP_Name='?'
*Result
"Alter Procedure...."

2. If not do you know other way to do it?

Thanks
Asaf Gilad

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-08 : 08:04:35

use OBJECT_DEFINITION:
http://msdn.microsoft.com/en-us/library/ms176090.aspx

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 08:07:25

[code]sp_helptext <SPNAME>[/code]
or
[code]select routine_definition from information_schema.routines
where routine_name = '<SPNAME>'[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 08:42:57

quote:

Originally posted by hanbingl

sp_helptext <SPNAME>

select routine_definition from information_schema.routines
where routine_name = '<SPNAME>'

the second query in blue wont give you complete definition if procedure body has more than 4000 characters. so either use OBJECT_DEFINITION as suggested by Mladen or use the below

SELECT definition from sys.sql_modules where OBJECT_NAME(object_id)='Your Procedure Name'

asafg
Starting Member

39 Posts

Posted - 2008-12-08 : 10:25:33

Thank you all,

I'm so happy to see that it's possible.

I got results with:
1:
select * from dbo.syscomments
where id = OBJECT_ID('sp_MyProc') (devided into lines)
2:
select routine_definition from information_schema.routines
where routine_name = 'sp_MyProc' (only the first part as promissed)

My questions are
1.1 Can I get the breaklines(new line) info as well?
1.2 Is there a way to get the entire procedure in one call? (not really necessary)

And some more questions regarding your answers
2.1 I can't find sp_helptext
2.2 I can't find sys.sql_modules
2.3 Actually I dont know where to look for information_schema.routines which works perfectly

Im using Microsoft SQL SERVER MANAGEMENT STUDIO 2005

Thanks a lot again
Asaf

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 11:38:06

[code]select replace(replace(text, char(10),' '),char(13),' ') from dbo.syscomments
where id = OBJECT_ID('YOUR SP') [/code]
This will get rid off all the line breaks.

asafg
Starting Member

39 Posts

Posted - 2008-12-08 : 11:43:20

:)

I want the line breaks - not to get rid of them
but I can't find them...

Get me the opposite to your solution and I'll be really happy :)
Thanks anyway - learned something new...

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 11:56:36

asafg, the text has line breaks already. If you are using Management studio 2005, you can have the query display in Text mode ([CTRL]+T ) then F5.

asafg
Starting Member

39 Posts

Posted - 2008-12-08 : 12:02:19

Sorry I tryed to copy the text into notepad and I saw no line breaks ...
A second ago I loaded the result into a text box and saw that the line breaks are there
and that I asked a stupid question

anyway now I know about the ctrl+T :)

Thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 12:11:29

cheers~~ no question's stupid~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources