0

Is it possible to use SchemaCrawler to retrieve a stored procedure's code?

I tried Routine.getDefinition() but it returned an empty string.

UPDATE: Here is my code:

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.slf4j.bridge.SLF4JBridgeHandler;
import schemacrawler.schema.Catalog;
import schemacrawler.schema.Column;
import schemacrawler.schema.Routine;
import schemacrawler.schema.Schema;
import schemacrawler.schema.Table;
import schemacrawler.schema.View;
import schemacrawler.schemacrawler.DatabaseConnectionOptions;
import schemacrawler.schemacrawler.RegularExpressionInclusionRule;
import schemacrawler.schemacrawler.SchemaCrawlerException;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaInfoLevelBuilder;
import schemacrawler.utility.SchemaCrawlerUtility;

/**
 * @author Gili Tzabari
 */
public class ExportScripts {

    public static void main(String[] args) throws SchemaCrawlerException, SQLException {
        SLF4JBridgeHandler.removeHandlersForRootLogger();
        SLF4JBridgeHandler.install();

        // Create a database connection
        final DataSource dataSource = new DatabaseConnectionOptions("jdbc:sqlserver://myDatabase;appName=SchemaCrawler;useCursors=true");
        final Connection connection = dataSource.getConnection("username", "password");

        // Create the options
        final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
        // Set what details are required in the schema - this affects the
        // time taken to crawl the schema
        options.setSchemaInfoLevel(SchemaInfoLevelBuilder.standard());
        options.setRoutineInclusionRule(new RegularExpressionInclusionRule("ContentGeneration\\.dbo.*"));
        options.setSchemaInclusionRule(new RegularExpressionInclusionRule("ContentGeneration\\.dbo.*"));

        // Get the schema definition
        final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection,
                options);

        for (final Schema schema : catalog.getSchemas()) {
            System.out.println(schema);
            for (final Routine routine : catalog.getRoutines()) {
                System.out.println("r--> " + routine);
                System.out.println("definition: " + routine.getDefinition());
            }
            for (final Table table : catalog.getTables(schema)) {
                System.out.print("o--> " + table);
                if (table instanceof View) {
                    System.out.println(" (VIEW)");
                } else {
                    System.out.println();
                }

                for (final Column column : table.getColumns()) {
                    System.out.println("     o--> " + column + " ("
                            + column.getColumnDataType() + ")");
                }
            }
        }
    }
}
Gili
  • 86,244
  • 97
  • 390
  • 689
  • Your question lacks a lot of detail. You need to provide background on what you have already done, along with some code. – Sualeh Fatehi Aug 17 '15 at 20:23
  • @SualehFatehi These are two separate questions. The "possible duplicate" question you linked to asks how to get the *list* of stored procedures in light of the `jtds` limitation. This question asks, assuming you're able to get that list, how do you get the stored procedure code. – Gili Aug 17 '15 at 20:26
  • Are you sure, that the code is there? Could be, that the procedure was created encrypted... Just try with pure SQL SELECT * FROM INFORMATION_SCHEMA.ROUTINES and look into the column "Definition". – Shnugo Aug 17 '15 at 20:33
  • @Shnugo yes, the code is there when I run the SQL statement you mentioned. – Gili Aug 17 '15 at 20:44

0 Answers0