How to query Intersystems Caché for a list of namespaces via SQL?
I am connecting to an intersystems cache database via C# and I need to obtain a list of all available namespaces.
I am using code like this:
// Source - https://stackoverflow.com/q/79393836
// Posted by Tony Valenti, modified by community. See post 'Timeline' for change history
// Retrieved 2026-04-21, License - CC BY-SA 4.0
namespace ConsoleApp13
{
internal class Program
{
static void Main(string[] args)
{
var CS = "Server=XXXXX;User=system;Password=sys;";
var C = new InterSystems.Data.CacheClient.CacheConnection(CS);
C.Open();
var Command = C.CreateCommand();
Command.CommandText = $@"
select * from %SYS.Namespace_List()
";
var Values = Command.ExecuteReader();
while (Values.Read()) {
}
}
}
}However, I get the error:
InterSystems.Data.CacheClient.CacheException: '[SQLCODE: <-99>:<Privilege violation>]
[Location: <Prepare>]
[%msg: <User system is not privileged for the operation>]'
It seems to say I don't have permissions, however, if I put a namespace in my connection string, it seems to work.
Does anyone have any ideas on how to get a list of namespaces (via SQL) that are available to a user?
Comments
To query InterSystems Caché for a list of namespaces via SQL, it seems there is no direct SQL command readily available to retrieve all namespaces across the platform as it might fall outside the SQL framework’s regular user privileges and scopes. Additionally, privilege issues, such as the “user system is not privileged for the operation” error, suggest that the targeted user lacks sufficient authorization.
Ensure the user accounts have appropriate system-level access permissions needed to interact at this scope. Tools like Cache Monitor might help visualize and interact with the database and obtain namespaces without requiring pre-configured extensive SQL commands or manual navigation 1.
Detailed guidance on overcoming privilege violations or alternative approaches is not covered in the retrieved documents. For direct solutions, consult the Developer Community for further assistance.
Sources:
Instead of
var CS = "Server=XXXXX;User=system;Password=sys;";
This may work
var CS = "Server=XXXXX;User=_system;Password=sys;";
_SYSTEM (with leading underscore !!) is the default System Manager User
not sure if sys or SYS for password (in Caché)
And YES you should use namespace %SYS
You may create an SQL PROCEDURE to serve your needs:
CREATE PROCEDURE %Z.NSPACE() RETURNS VARCHAR
Language ObjectScript
{
set (%,%r)=""
for set %=$o(^|"%SYS"|SYS("CONFIG","CACHE","Namespaces",%)) quit:%="" set %r=%r_%_";"
quit %r
}and it runs like this, assuming you have access to Namespace %SYS
USER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>select %Z.NSPACE()
3. select %Z.NSPACE()
Expression_1
%ALL;%SYS;DOCBOOK;SAMPLES;USER;
1 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0006s/5/160/0ms
execute time(s)/globals/lines/disk: 0.0005s/6/425/0ms
cached query class: %sqlcq.USER.cls4
---------------------------------------------------------------------------
USER>>