Azure DW default schema in a query / proc equal to "Search_Path" in AWS

  • I know you can't do this in MSSQL, but hoping DW has progressed more.

    Search_path in AWS / Postegre.

    "Specifies the order in which schemas are searched when an object (such as a table or a function) is referenced by a simple name with no schema component:"

    I started a new job with heavy DW and for different customers they have identical procs, just pointing to diff schemas for each customer, and as we grow thats going to be a nightmare. I want to be able to use a set command, or something and have DW default the schemas so we do have to keep 9 and counting identical versions of code.

    Something like this:

    SET @SearchSchema = 'AbcSchema".

    SELECT * from <TableName> where it serarchs the ABCSCEHMA across all queries where a different schema is not referenced.. It really is a handy feature.  If not is there a workaround without dynamic SQL, maybe declare a sysname variable used as schema then reference that in a select or update..?

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply