July 15, 2009 at 12:35 pm
Hi guys, sorry to bother with such single question. But i dont know if it can be done.
I have a database with a craploads of store procedures, everytime i want to see the code for a procedure and i go to the folder where those are showed in the object explorer, it takes forever to show me the list.
Obviously i dont have *.SQL file with the procedure. (Damn u client!!!)
Is there an instruction to see the code of certain SP by just providing the name?
something like " showCode [dbo].[myStoreProcedure] "
hope it exists, that list takes about 20 minutes to show ¬¬'
July 15, 2009 at 12:46 pm
You can select the definition column from sys.sql_modules. That has the object definition in it (the create script).
Using object_id, you can get the one you want if you have the name, or you can use Like to get to it, since the proc name is part of the definition.
Ends up looking like:
select definition
from sys.sql_modules
where object_id = object_id(N'proc name');
Put the name of the proc in where I have "proc name".
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2009 at 12:50 pm
This works with all versions of SQL to look at the code for stored procedures, functions, and views:
exec sp_helptext 'MyProcedureName'
July 15, 2009 at 1:05 pm
Thanks for the fast replys, i used both suggestions and both worked,
the diference is that sp_helptext gives me rows with fragments of code, and those look like a wall of text that scare me XD
the first one helped me having just 1 row, was easier to give format to the code.
Im happy that the store was not to big O.o
Thanks guys 😀 😀
July 15, 2009 at 1:33 pm
zenrigar (7/15/2009)
Thanks for the fast replys, i used both suggestions and both worked,the diference is that sp_helptext gives me rows with fragments of code, and those look like a wall of text that scare me XD
the first one helped me having just 1 row, was easier to give format to the code.
Im happy that the store was not to big O.o
Thanks guys 😀 😀
Okay, using sp_helptext while in grid mode does not work very well for display purposes. What I do is:
{ctrl-t} set text mode
{ctrl-e} execute the query
{ctrl-d} put it back in grid mode
One other note: if you select the column in grid mode and copy/paste that into the query window it works just fine.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply