Read Stored procedure text from java code.

Hello all,
Is there an API available in java, to read the contents of a Stored proc in an SQL server DB? We have about 3000 stored procs and want to create a backup of all of them.
Thanks in advance
spankenstienAsked:
Who is Participating?
 
Duane LawrenceConnect With a Mentor Commented:
I do what I said above and just copy it all to a text file, then search all I want.  But if you have to do it with a program see below.



create proc foo
as
--1.
    select o.id, o.name, o.uid, user_name(o.uid) as username into #temptable
    from dbo.sysobjects o
    where o.type = 'P'

--2. cursor to iterate through all proc names and put the text into another table.

--3. use dts to export to a file

go

Duane
0
 
spankenstienAuthor Commented:
forgot to mention this. we are using JSQLConnect to connect to SQL server DB
0
 
Duane LawrenceCommented:
This may not be the java answer you want, but it is a lot easier.  

In enterprise manager, go to the desired database, expand the stored procedure catigory, right click on a stored proc, go to "all tasks", then to "Generate SQL Script", click on the show all button and put a check mark next to "all stored procedures"

Duane
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
SRigneyConnect With a Mentor Commented:
the system stored procedure sp_helptext takes a stored procedure name, so for example you could use

sp_helptext sp_CustomerGetByName

And the output would be the text that is in the stored procedure sp_CustomerGetByName.

This works for views and triggers as well.
0
 
spankenstienAuthor Commented:
Duane,
The reason I need to access the text in java is to look for at particular string and see it is commented.i should given more details. currently im calling "sp_helpText SPName" , looping thru the text and calculating based on the occurences of "/*" and "*/" , relative to the String i am looking for.
0
 
arbertCommented:
I prefer not to use the system tables.  You can look at information_schema.routines:

select routine_name,routine_definition from information_schema.routines

You can use Charindex and Substring to find your comment marks....NOW, the only caveat I would give about the information_schema views for the routines is they only return 4000bytes for the routine.  So, if it's over 4000bytes, you do have to use the system tables.

Brett
0
 
spankenstienAuthor Commented:
sample code:

        String[][] spNames = new String[1][1];
        String[] spName = new String[1];
        try {
            spNames = dbConn.executeSQL("select distinct so.name from syscomments sc \n" +
                    "\tinner join sysobjects so on sc.id = so.id \n" +
                    "\twhere xtype = 'P' \n" +
                    "\tAND charindex('psg_prog_alleg_id', text) > 0");
        } catch (Exception e) {
            e.printStackTrace();
        }

    outer: for(int i = 0 ;i <spNames.length ; i++){
            spName = spNames[i];

            String[][] sp = new String[1][1];
            try {
                sp = dbConn.executeSQL("sp_helptext " + spName[0]);
            } catch (Exception e) {
                e.printStackTrace();
            }
            String allText = "";
            for (int k = 0; k < sp.length; k++) {
                allText += sp[k][0];
            }
            int psgIndex = allText.lastIndexOf("psg_prog_alleg_id");
            if(psgIndex == -1 )
                psgIndex = allText.lastIndexOf("Psg_prog_alleg_id");
            if(psgIndex == -1 )
                psgIndex = allText.lastIndexOf("PSG_PROG_ALLEG_ID");
            if(psgIndex == -1 )
                psgIndex = allText.lastIndexOf("Psg_Prog_Alleg_id");
            if(psgIndex == -1){
                System.out.println("\n\n\n " + spName[0] + "\n\n\n");
                return;
            }

            String firstHalf = allText.substring(0,psgIndex);
            String secondHalf = allText.substring(psgIndex,allText.length());
            int firstHalfCS = firstHalf.lastIndexOf("/*");
            int firstHalfCE = firstHalf.lastIndexOf("*/");
            if(firstHalfCE < firstHalfCS){
                System.out.println("commented" + spName[0]);
                continue outer;
            }

            PrintWriter out1 = new PrintWriter(new BufferedWriter(new FileWriter("C:\\backup\\Allegation\\10-26\\dev\\"+spName[0] +".txt",true)));
            for (int j = 0; j < sp.length; j++) {
                String[] strings = sp[j];
                out1.println(strings[0]);
                //System.out.println("value: " + strings[0]);
            }
            out1.close();

        }
0
 
arbertCommented:
That might not totally work with SQL2005....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.