4

I need to obtain a list of namespaces, databases, and tables in Caché so I can loop through each and query various things (like row counts).

I've seen this, but it only lists the databases within the %SYS namespace.

I know I can use LIST^%NSP in the terminal to obtain a list of namespaces, but it has extra text ("Here are the defined namespaces:") that I don't want to have to parse. Plus, I really would like to stick to SQL queries if possible.

Thanks

Community
  • 1
  • 1
user2063351
  • 503
  • 2
  • 13
  • 31

2 Answers2

4

In the terminal you can write the following commands:

D ##class(%SYS.Namespace).ListAll(.result)
zw result

This will fetch you all the available namespaces.

olleh
  • 1,915
  • 18
  • 21
2

To list namespaces, you want the List query in the %SYS.Namespace package.

To retrieve data about the contents of a specific namespace, you will need to be in that Namespace. From COS, you can use ZNSPACE. Then, you can query %Dictionary.ClassDefinition, as linked. Contrary to your statement, this does not query only %SYS classes, but all classes available from the current namespace, which will (by default) include all classes that begin with a %.

If you would like to avoid those classes, you could simply call

SELECT Name FROM %Dictionary.ClassDefinition WHERE NOT Name %STARTSWITH '%'
Brandon Horst
  • 1,921
  • 16
  • 26
  • As a further note on this, you can call the abovementioned List query as a stored procedure (at least on newer versions): `CALL %SYS.Namespace_List()` It should be relatively easy to process the returned result set, but that may depend on your client. You also have to consider whether the system has any package mappings (where the same definitions/libraries are available in multiple namespaces) or global mappings (where the same data may be available in multiple namespaces). That may affect how you view/process your results. – DdP Nov 21 '13 at 13:25