how to describe and show table in DERBY DB?

Question

I have this SQL query

1 : show tables
2 : desc tablename

But this doesn't seem to be the syntax in the derby.

How to write these queries in derby ??

I want to check the schema of a table whether its primary key or not.

How to check that in websphere

Answer 1

Show tables via a query (no IJ):

select st.tablename  from sys.systables st LEFT OUTER join sys.sysschemas ss on (st.schemaid = ss.schemaid) where ss.schemaname ='APP'

Show columns via a query (no IJ):

select * from sys.syscolumns where referenceid = (select tableid from sys.systables where tablename = 'THE_TABLE') order by columnnumber

Answer 2

Strictly speaking, this isn't SQL. Rather, these are IJ commands, and must be processed by the IJ tool.

Here's the documentation for "describe": http://db.apache.org/derby/docs/10.10/tools/rtoolsijcomrefdescribe.html

And here's the documentation for "show tables": http://db.apache.org/derby/docs/10.10/tools/rtoolsijcomrefshow.html

You don't run these commands in Websphere, you run them in IJ.

Answer 3

Two ways that I know of to do so without JOINS etc.:

try {

    Connection databaseConnection;
    //Establish Connection Through Embedded Or Local Install
    DatabaseMetaData metaDataForDatabaseConnection = databaseConnection.getMetaData();
    ResultSet resultSetForTableNames = metaDataForDatabaseConnection.getTables(null, null, null, new String[]{"TABLE"});


    while (resultSetForTableNames.next()) {
        System.out.println(resultSetForTableNames.getString(3));
    }

    //Close Resources As Necessary
}
catch (Exception e) {
    e.printStackTrace();
}

And:

try {   

    Connection databaseConnection;
    //Establish Connection Through Embedded Or Local Install
    Statement databaseStatement = databaseConnection.createStatement();
    ResultSet resultSet = databaseStatement.executeQuery("SELECT SYS.SYSTABLES.TABLENAME FROM SYS.SYSTABLES WHERE SYS.SYSTABLES.TABLETYPE = \'T\'");


    while (resultSet.next()) {

        System.out.println(resultSet.getString("TABLENAME"));
    }

    //Close Resources As Necessary
}
catch (Exception e) {
    e.printStackTrace();
}

Answer 4

    describe name_table;

It works, it shows all description and columns and features of the table you want to know.