Using System Tables

System tables that can be used to retrieve database, table or column metadata.

Retrieving Database Metadata

To retrieve the database metadata, execute a query against the sys_tables table.

SELECT select_list FROM sys_tables

select_list = * | { column_name [ AS column_alias ] } [ ,...n ]

The example below shows how to retrieve the table names of all the tables in MSCRM.

String connectionString = "user=myuseraccount;password=mypassword;URL=http://myserver/myOrgRoot;Offline=false;";
Connection conn = DriverManager.getConnection(connectionString);
Statement stat = conn.createStatement();

boolean ret = stat.execute("SELECT * FROM sys_tables");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  System.out.println(rs.getString("TABLE_NAME"));
}

We also support the conventional JDBC approach to retrieve the database metadata.

DatabaseMetaData table_meta = conn.getMetaData();
ResultSet set=table_meta.getTables(null, null, "%", null);  
//"%" indicates that we want to list all tables
while(rs.next()){
  System.out.println(rs.getString("TABLE_NAME"));
}

Retrieving Table Metadata

Table metadata includes the columns names, the data types, the table key information, etc. To retrieve the database metadata, execute a query against the sys_tablecolumns table.

SELECT select_list FROM sys_tablecolumns WHERE TableName=table_name

select_list = * | { column_name [ AS column_alias ] } [ ,...n ]

The example below shows how to retrieve all the columns of the Lead table.

boolean ret = stat.execute("SELECT * FROM sys_tablecolumns WHERE TableName=Lead");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  System.out.println(rs.getString("COLUMN_NAME"));
}

We also support the conventional JDBC approach to retrieve the table metadata

DatabaseMetaData table_meta = conn.getMetaData();
ResultSet rsColumns = table_meta.getColumns(null, null, "Lead", null);
while(rs.next()){
  System.out.println(rs.getString("COLUMN_NAME"));
}