need to script a procedure into an output file
-
Hi All,
I know we could get the script of a stored procedure in Enterprise Manager. However I need to do this using a utility in dos prompt. I need to specity a stored procedure and script out to an output file.
Is this possible?
Thank you!
-
One thing you could use (though you'll have to do a little cleanup) is use osql and the sp_helptext system stored procedure. For instance
osql -E -S MyServer -d Northwind -Q "exec sp_helptext CustOrderHist" -o sproc.txt
Here I'm making a connection to MyServer, using the Northwind database, via a trusted connection (-E) and creating an output file called sproc.txt.
Now the one thing is there is a header due to sp_helptext and that'll have to be scrubbed off (remove anything before CREATE PROC).
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 01/29/2002 09:22:49 AM
K. Brian Kelley
@kbriankelley -
BTW, I should mention that isql works, too.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Thank you so very much. It works but if the sp is encrypted then the script doesn't created. Am I right on this?
-
That is correct. There are a few places where you can get decrpytion routines (hence these are not that secure). Is this version v6.5 or v7/2000?
Steve Jones
-
As Steve has indicated, the decryption routines do exist, people have figured 'em out. But through Microsoft's provided tools there is no way. For instance, going through EM and trying to view an encrypted stored procedure will get you the following:
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
thankyou so much for all your help! my working environment is sql server 7.0!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply