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

• digdave7

  Ten Centuries

  Points: 1026

  More actions

  June 8, 2023 at 4:52 pm

  #4207173

  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..?

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  June 9, 2023 at 5:10 pm

  #4207966

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

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