Getting database names from dictionary metadata of Snowflake library references.

[LaurieF]

I'm trying in DI user-written transformations to automagically generate SQL connect clauses from libname statements. In other DBMSs, I pick out the schema information from dictionary.libnames (where sysname like 'Schema%'). This works for Snowflake too, but there's no row in libnames for anything like sysname like 'Database%'. 

When I productionise this code, the schema name is likely to stay the same but the database name will change, and so I want the process to be dynamic. Has anyone had any experience doing this?

[Tom]

First get rid of the WHERE clause (or just filter on LIBNAME) so that you can see what is stored.  Different engines will store different information (and different language settings will change how the values are stored also).

What engine are you using the connect to Snowflake?  Does SAS have a specific engine for it (like ORACLE or TERADATA)? Or are you using e generic connection (like ODBC).

Did you research if there is a SQL query you can push into the Snowflake database to tell you what your default schema/database is for your current session?  Does  Snowflake even have such a concept?

[LaurieF]

I've done it without the where clause, and that doesn't help. It provides Schema, Server Version and Client Version only. I'm using SASIOSNF, which I think is either ODBC or JDBC. 

And remember I'm trying to programatically generate the sql connect clause, so that I am able to then execute pass-through code - cart before the horse, sadly. 

[Tom · Accepted solution]

I would forget the horse and buggy and get a car instead.

Just the CONNECT USING statement in PROC SQL.

proc sql;
connect using mylib ;
select * from connection to mylib
(  ... snowflake code here ...)
;
quit;

[LaurieF]

Getting a car to replace the horse and buggy would be a bit of A Mishion…

And it worked. Genius. I've been using connect clauses for 25 years, and I didn't know about that syntax.

I owe you a beer, when you're allowed to enter Aotearoa.