Archive for August, 2013

Trick to unhide the sqltext of a procedure

August 2nd, 2013 1 comment

One fine morning  you want to see the code for a stored procedure but what happen if the text of the proc is hideen through sp_hidetext ?
I am sure you guys know that, once a stored proc is hidden through sp_hidetext, this can not be reverse .

This will even get worse if you do not have a OS level file backup of that proc any where on your server machines or local machines . Even your DBA friends do not have the back up on their desktops/laptops.  This seems to be a very rare scenario but some times it happens 🙂
I was in this challenging  scenario … was able to find a crack to unhide the code for a hidden proc . Sharing on this forum.

Here you GO!

1>Shutodwn <sybase_server>

2>Start sybase server on diagnostic mode.

$SYBASE/ASE-15_0/bin>  diagserver -usa -s<sybase_server> -p”password” -d<Master_device_path>

3>use <database_name>

create table #m( id int)
insert into #m values(object_id(“proc_name”))

dbcc _unhide_text(‘#m’)
sp_helptext “proc_name”    ———You can see the proc text now !!! HURRY!!!

4> checkpoint
Shutdown the sybase_server

5>Start sybase_server in normal mode

 Note – Pls. do not try this on your production set up as this is not  documented…but you are open to load your production data on UAT/dev and explore.

Categories: ASE Tags: