Get schema list from ODBC database

[#1]

Hi all.

Im about to make a script, that fetches tables from an ODBC database. I have managed to use the query builder to do this, but I need to be able to select a schema from a drop down list, as not all users will have access to all schemas. Can anyone help me with an example on how to get the available schemas as a list or similar? It doesnt need to exclusively be the schemas the user has access to, but just all the schemas in the database. As an extra issue, it would be cool if I could get a list of tables that all have a specific view.

Thanks in advance!

Br. Anders

[#2 · Accepted solution]

This is how I'd do it in Oracle:

dbc = create database connection("<your connection string here>");
sql = "SELECT a.username
  FROM all_users a
 ORDER BY a.USERNAME";
dt = execute sql(dbc, sql, invisible)
close database connection(dbc);
schema_list = dt:username << get values;
close(dt, nosave);

tbl_list = {};

nw = new window("Schema Browser",
	panel box("Schemas",
		schema_cb = combo box(schema_list,
			one_schema = schema_cb << get selected;
			sql = evalinsert(
"SELECT a.table_name
   FROM all_tables a
  WHERE a.owner = '^one_schema^'
  ORDER BY 1");
			dbc = create database connection("<your connection string here>");
			dt = execute sql(dbc, sql, invisible)
			close database connection(dbc);
			tbl_list = dt:table_name << get values;
			tbl_lb << set items(tbl_list);
			close(dt, nosave);
		),
	),
	panel box("Schema Tables",
		tbl_lb = list box(tbl_list, width(300), nlines(20))
	),
);

[#3]

Thanks so much, this helped a lot!