spankenstien
asked on
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
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I prefer not to use the system tables. You can look at information_schema.routine s:
select routine_name,routine_defin ition from information_schema.routine s
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
select routine_name,routine_defin
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
ASKER
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_help text " + 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_p rog_alleg_ id");
if(psgIndex == -1 )
psgIndex = allText.lastIndexOf("Psg_p rog_alleg_ id");
if(psgIndex == -1 )
psgIndex = allText.lastIndexOf("PSG_P ROG_ALLEG_ ID");
if(psgIndex == -1 )
psgIndex = allText.lastIndexOf("Psg_P rog_Alleg_ id");
if(psgIndex == -1){
System.out.println("\n\n\n " + spName[0] + "\n\n\n");
return;
}
String firstHalf = allText.substring(0,psgInd ex);
String secondHalf = allText.substring(psgIndex ,allText.l ength());
int firstHalfCS = firstHalf.lastIndexOf("/*" );
int firstHalfCE = firstHalf.lastIndexOf("*/" );
if(firstHalfCE < firstHalfCS){
System.out.println("commen ted" + spName[0]);
continue outer;
}
PrintWriter out1 = new PrintWriter(new BufferedWriter(new FileWriter("C:\\backup\\Al legation\\ 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("valu e: " + strings[0]);
}
out1.close();
}
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_
} 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_help
} catch (Exception e) {
e.printStackTrace();
}
String allText = "";
for (int k = 0; k < sp.length; k++) {
allText += sp[k][0];
}
int psgIndex = allText.lastIndexOf("psg_p
if(psgIndex == -1 )
psgIndex = allText.lastIndexOf("Psg_p
if(psgIndex == -1 )
psgIndex = allText.lastIndexOf("PSG_P
if(psgIndex == -1 )
psgIndex = allText.lastIndexOf("Psg_P
if(psgIndex == -1){
System.out.println("\n\n\n
return;
}
String firstHalf = allText.substring(0,psgInd
String secondHalf = allText.substring(psgIndex
int firstHalfCS = firstHalf.lastIndexOf("/*"
int firstHalfCE = firstHalf.lastIndexOf("*/"
if(firstHalfCE < firstHalfCS){
System.out.println("commen
continue outer;
}
PrintWriter out1 = new PrintWriter(new BufferedWriter(new FileWriter("C:\\backup\\Al
for (int j = 0; j < sp.length; j++) {
String[] strings = sp[j];
out1.println(strings[0]);
//System.out.println("valu
}
out1.close();
}
That might not totally work with SQL2005....
ASKER