Marc Davis
asked on
Sybase stored procedure extract with wrappers
All,
I know DEFNCOPY can extract the contents of a stored procedure but is there a way to include get the header and footer (security) wrappers of the stored procedure?
Something like:
IF OBJECT_ID('dbo.MYTEST) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.MYTEST
IF OBJECT_ID('dbo.MYTEST') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.MYTEST >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.MYTEST >>>'
END
go
the CREATE PROCEDURE and everything after it comprising the stored procedure can extracted using DEFNCOPY but then I have the security footer wrapper like...
IF OBJECT_ID('dbo.MYTEST) IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.MYTEST >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.MYTEST >>>'
go
EXEC sp_procxmode 'dbo.MYTEST','unchained'
go
Is there a way or tool that can extract out all that information in an automated fashion? I'm trying to write a script to pull that appropriate information and to create files of the stored procedures. DEFNCOPY only gave me part of it but I'm looking for the other part too.
Any assistance would be greatly appreciated.
I know DEFNCOPY can extract the contents of a stored procedure but is there a way to include get the header and footer (security) wrappers of the stored procedure?
Something like:
IF OBJECT_ID('dbo.MYTEST) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.MYTEST
IF OBJECT_ID('dbo.MYTEST') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.MYTEST >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.MYTEST >>>'
END
go
the CREATE PROCEDURE and everything after it comprising the stored procedure can extracted using DEFNCOPY but then I have the security footer wrapper like...
IF OBJECT_ID('dbo.MYTEST) IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.MYTEST >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.MYTEST >>>'
go
EXEC sp_procxmode 'dbo.MYTEST','unchained'
go
Is there a way or tool that can extract out all that information in an automated fashion? I'm trying to write a script to pull that appropriate information and to create files of the stored procedures. DEFNCOPY only gave me part of it but I'm looking for the other part too.
Any assistance would be greatly appreciated.
ASKER
I'm looking for something to use from an automatiion script standpoint.
Is there a way in Sybase, from a command line, prospective that the DDL can be generated? DEFNCOPY is the closest I've been but it doesn't cover everything.
Are there some SQL's I can use to get the header and footer information. I can use DEFNCOPY and use those SQL's in the same script scenario to generate the info but I don't know what those SQL's would be.
Do you happen to know?
Is there a way in Sybase, from a command line, prospective that the DDL can be generated? DEFNCOPY is the closest I've been but it doesn't cover everything.
Are there some SQL's I can use to get the header and footer information. I can use DEFNCOPY and use those SQL's in the same script scenario to generate the info but I don't know what those SQL's would be.
Do you happen to know?
If you want to do it on the commandline, then you may have to have Sybase version 12 and above
Above version supplied "ddlgen' command to generate the procedure including header and footer.
--C.M
Above version supplied "ddlgen' command to generate the procedure including header and footer.
--C.M
ASKER
I have Sybase 12 client but I'm not seeing this DDLGEN anywhere. I thought it might be in the same location under BIN as the DSEDIT and DEFNCOPY but it's not there.
I did a search for "DDLGEN" on my entire system and it didn't find it.
Where can I get that at?
I did a search for "DDLGEN" on my entire system and it didn't find it.
Where can I get that at?
This tool is availabe from 11.9.2
The ddlgen should be there at $SYBASE/bin like defncopy. Otherwise, ask your DBA to install it on the $SYBASE/bin.
See the below lick and get more info:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/13262
The ddlgen should be there at $SYBASE/bin like defncopy. Otherwise, ask your DBA to install it on the $SYBASE/bin.
See the below lick and get more info:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/13262
ASKER
Let me take a look into this and I'll ask one of our sybase DBA's.
I'll report back soon (within the next day) once I get it and try it out.
I'll report back soon (within the next day) once I get it and try it out.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why don't you creat a unix shell script that will do the trick?
All you need to do is run isql from the shell script an execute the "sp_helptext" sybase command
which will print the content of the sp. If you need the header and footer you can a section to the shell script
to generate them.
Here is sothing i use:
****
#!/usr/bin/ksh
# Parameters
ISQL="$SYBASE/OCS-12_0/bin /isql -Usa -P -S$SYBSERV -w120"
database=$1
procedure=$2
tmp_file="/tmp/sp.$$"
# Header
echo "use $database"
echo "go\n"
echo "IF OBJECT_ID('$procedure') IS NOT NULL"
echo "BEGIN"
echo " DROP PROCEDURE $procedure"
echo "IF OBJECT_ID('$procedure') IS NOT NULL"
echo " PRINT '<<< FAILED DROPPING PROCEDURE $procedure >>>'"
echo " ELSE"
echo " PRINT '<<< DROPPED PROCEDURE $procedure >>>'"
echo "END"
echo "\ngo\n"
# Text
$ISQL << ! > ${tmp_file}
use $database
go
set nocount on
go
sp_helptext $procedure
go
!
cat ${tmp_file} | grep -v "return status"| tail +8
echo "go\n"
# Footer
echo "IF OBJECT_ID('$procedure') IS NOT NULL"
echo " PRINT '<<< CREATED PROCEDURE $procedure >>>'"
echo "ELSE"
echo " PRINT '<<< FAILED CREATING PROCEDURE $procedure >>>'"
echo "go\n"
echo "EXEC sp_procxmode '$procedure','unchained'"
go
# Clean
rm ${tmp_file}
****
All you need to do is run isql from the shell script an execute the "sp_helptext" sybase command
which will print the content of the sp. If you need the header and footer you can a section to the shell script
to generate them.
Here is sothing i use:
****
#!/usr/bin/ksh
# Parameters
ISQL="$SYBASE/OCS-12_0/bin
database=$1
procedure=$2
tmp_file="/tmp/sp.$$"
# Header
echo "use $database"
echo "go\n"
echo "IF OBJECT_ID('$procedure') IS NOT NULL"
echo "BEGIN"
echo " DROP PROCEDURE $procedure"
echo "IF OBJECT_ID('$procedure') IS NOT NULL"
echo " PRINT '<<< FAILED DROPPING PROCEDURE $procedure >>>'"
echo " ELSE"
echo " PRINT '<<< DROPPED PROCEDURE $procedure >>>'"
echo "END"
echo "\ngo\n"
# Text
$ISQL << ! > ${tmp_file}
use $database
go
set nocount on
go
sp_helptext $procedure
go
!
cat ${tmp_file} | grep -v "return status"| tail +8
echo "go\n"
# Footer
echo "IF OBJECT_ID('$procedure') IS NOT NULL"
echo " PRINT '<<< CREATED PROCEDURE $procedure >>>'"
echo "ELSE"
echo " PRINT '<<< FAILED CREATING PROCEDURE $procedure >>>'"
echo "go\n"
echo "EXEC sp_procxmode '$procedure','unchained'"
go
# Clean
rm ${tmp_file}
****
ASKER
The DDLGEN worked out. I had to do a few thing to get rid of the DDL comments and put in the header/footers. I used a Perl script to perform that task.
It is working out great and very much appreciate the input and I am so sorry for the delay.
yoav_aviram, this process would not be available to run under Unix so the korn shell is not a viable option. SP_HELPTEXT when looked out before required more adjustments that that of the DDLGEN.
Thanks for the help and input all. Greatly appreciated!
It is working out great and very much appreciate the input and I am so sorry for the delay.
yoav_aviram, this process would not be available to run under Unix so the korn shell is not a viable option. SP_HELPTEXT when looked out before required more adjustments that that of the DDLGEN.
Thanks for the help and input all. Greatly appreciated!
I dont think in sybase have commond to get a entire procedure (including header and footer). But If you have Sybase Central on your PC, then you can able to download the procedure including header and footer (Dont expect much from this.. but it is better that DFNCPY commond.
here is the output of my procedure which was generated by 'Generate DLL ' command from Sybase Central.
--------------------------
print 'spInsChakuchiData'
SETUSER 'dbo'
go
create procedure spInsChakuchiData
@AsOfDate smalldatetime
as
begin
....
....
end
go
SETUSER
go
grant Execute on spInsChakuchiData to public
go
--------------------------
I hope it may help you out.
--C.M