Need a table or schema API

Question

Question

Laura Cavanaugh · Jul 26, 2016

i'm looking for a way to display a list of tables in an application. I need a Table or Schema API, and I can't find one. I found this:

http://docs.intersystems.com/latest/csp/docbook/%25CSP.Documatic.cls?PAG...

with a google search, but I'm not sure if I can even use this (it says that SQL name is "TABLES"), or how to use it.

Is there a way to get at the table names available in a namespace?

Thanks,

Laura

Discussion (10)0

Log in or sign up to continue

Marc Mundt · Jul 26, 2016

You can query the table you found like this:
select * from INFORMATION_SCHEMA.TABLES

I note from the docs you linked that this will only show tables that the current user has privileges for.

2 0

Marc Mundt · Jul 26, 2016

You can see this table in the management portal if you tick the "System" checkbox to display Caché system tables.

0 0

score 0 · Answer 1 · 2016-07-26T15:13:22-04:00

Yes, the user would be some kind of admin who has access to all the tables needed to display. And, INFORMATION_SCHEMA.TABLES is not found in the management portal, anyway.

Thanks,

Laura

score 1 · Answer 2 · 2016-07-26T15:52:01-04:00

You can see this schema/table in management portal if you tick the "System" checkbox to display Caché system tables. Queries against this table will work even if the table doesn't appear in the list.

score 0 · Answer 3 · 2016-07-27T08:18:40-04:00

Timothy Leavitt · Jul 27, 2016

Note that INFORMATION_SCHEMA was introduced in Caché 2015.1.

0 0

score 0 · Answer 4 · 2016-07-27T08:36:00-04:00

Oh that looks lovely, and it works great on my local 2016 cube, but not on my server which is 2014. I forget to check the latest version sometimes.

Oh well.

Thanks,

Laura

score 1 · Answer 5 · 2016-07-27T09:23:14-04:00

If you are on an older system where INFORMATION_SCHEMA is not available, you can use the %Library.SQLCatalog class queries. Here is an example:

#include %occStatus
   new stmt,sc,rset,handle
   set stmt=##class(%SQL.Statement).%New()
   set sc=stmt.%PrepareClassQuery("%Library.SQLCatalog","SQLTables")
   if $$$ISERR(sc) { write $System.Status.DisplayError(sc) QUIT }
   set rset=stmt.%Execute()
   do rset.%Display()
   QUIT

score 0 · Answer 6 · 2016-07-26T15:21:38-04:00

Well, I'm getting the data with %Dictionary.CompiledClass. It's not quite the same as a SQL table, but it will do. if you do have any info on some kind of SQL API, however, let me know.

Thanks,

Laura

e.g.

Select Name, SqlTableName from %dictionary.compiledclass where Name [ 'Data'

score 0 · Answer 7 · 2016-07-27T01:55:52-04:00

Hi, Laura!

Not sure if it is an answer, but have you seen Class Explorer app?

It can show all the classes in namespace or package.

score 0 · Answer 8 · 2016-07-27T08:43:47-04:00

Yes, I have! And it looks like a great app, but I haven't installed it yet. I'm not sure if that would give me access programmatically to the tables though; trying to populate a table of table names for security setup. Thanks for the app!

Laura