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
