Procedures Information

Hi all
In Oracle if we want to get the procedures,functions or packages information we can write the stmt like this

select text from user_source where name='' and type=''

In sqlserver is it possible to get the information. I know that we are having a stored procedure sp_procedures but i don't know how to get the information from that because of its structure

Baba

Use the following query..

Code Snippet

Exec sp_helptext 'Your Sp Name'

|||If you're using SQL2005 you can use

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_yourproc'))

|||

Note:

OBJECT_DEFINITION wont return the full soruce of you sp if you suffix the numbers (grouped stored procedures).

Code Snippet

Create Proc #Test

as

Begin

Select 1

End

Go

Create Proc #Test;2

as

Begin

Select 2

End

Go

Create Proc #Test;3

as

Begin

Select 3

End

Exec tempdb..sp_helptext '#Test' --Correct; Better to use on any version

SELECT OBJECT_DEFINITION(OBJECT_ID('tempdb..#Test')) --Only Fetch the first sp group 2 & 3 missing

|||

Thanks for pointing that out- have never used grouped stored procedures before. Is there a common scenario when you may wish to go this route?

Cheers

Rich