Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: Extract Stored Procedure code

    All,

    Can anyone tell me how to extract a stored procedure code from the database as one file(.sql) per stored procedure?

    I need one file each for one stored procedure not all stored procedure code in one file. Is it possible to do it with some tools or any specific procedures?

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Have a look at the ddlgen utility

  3. #3
    Join Date
    Mar 2008
    Posts
    96
    You can use defncopy for extracting stored procedure code as follows
    defncopy -U<user_name> -S<server_name> out <output_file_name.txt> <db_name> <owner_name.procedure_name>

    Hope this will helpful

  4. #4
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    As far as I know, ddlgen doesn't let you get the SP's into separate files, rather it extracts them into one big file. But with a one line select and a little shell script you can get the desired effect. ddlgen can be tricky to use, so I've included the command below in the example code for the shell script.

    First get a list of stored procs from your database with:
    select name from sysobjects where type = "P"
    Then copy the sp names into a file, eg, sp_list.txt
    Then create a shell script which should contain a loop something like:

    cat sp_list.txt | while read stored_proc_name
    do
    /opt/sybase/ASEP/bin/ddlgen -Usa -P<sa_password> -S<ASE_NAME> -TP -N${stored_proc_name} -D<database_name> -O${stored_proc_name}.sql
    done

    If you're using Linux with Public Domain ksh, then this loop construct won't work; let me know if that's the case and I'll supply the PD bash alternate construct.
    Naturally, you don't have to use the actual sa login for this, I've just used it for the sake of this example.

    Hope that helps.

    Bob
    Remember to modify the path to ddlgen to suit your environment.
    Last edited by harq; 06-03-09 at 06:18.
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

  5. #5
    Join Date
    May 2009
    Posts
    17

    Extract stored procedure

    Hi,

    Thanks for the reply

    I am trying to do this in Windows...

    Thanks
    Somasundaram

  6. #6
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    Ah, right. Slight problem there then! In that case, and in case you don't think you can achieve the same with a batch file, then I would suggest downloading Cygwin; this will give you a lightweight yet totally comprehensive Unix environment on a Windows machine. It's fairly straight forward to download and install and you can run the standard DOS commands within the same evironment, ie, you can call standard Sybase isql for DOS within a Cygwin shell. If you want to go that route then you can download get Cygwin from www.cygwin.com. Cygwin comes with the PD bash shell. Incidentally, this would then open up the realm of other shell scripts....

    Bob
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •