Need a table or schema API
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?PA…
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
Comments
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.
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
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.
Note that INFORMATION_SCHEMA was introduced in Caché 2015.1.
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
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
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'
You can see this table in the management portal if you tick the "System" checkbox to display Caché system tables.
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.
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