4

SQL allows easy setting/changing current database with use xxx_db; statement but it looks like it does not allow changing the current schema. There is similar question for Postgres, I see on the internet this for ORACLE:

ALTER SESSION SET CURRENT_SCHEMA = joe;

What about MS SQL Server?

AFAIK, the default schema is set in the properties of the user. This means that queries (or stored procedures) that are intended to be run by different users should specify the schema explicitly (who knows what is the setting of the user plus SQL code will get broken if somebody will change the user setting).

Maybe somebody knows the rationale why this is not important? Is it really true that typical SQL code is strongly tied to the setting of the user?

Can it be that using schemas for anything different from organizing security is not recommended?

Buran
  • 135
  • 1
  • 11
  • Why do you want to change schema? What are you trying to do? – shree.pat18 Jan 25 '17 at 09:37
  • I want to understand if I always should write the schema explicitly? How should I write code if I want to group tables logically (not for security reasons), etc. – Buran Jan 25 '17 at 09:45
  • You can put tables in a schema, grant SELECT on that schema to the user and write your select query as 'select from .'. Are you trying to avoid writing '.
    ' by setting it earlier?
     – shree.pat18 Jan 25 '17 at 09:48
  • You may want to look at this as well: https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx – shree.pat18 Jan 25 '17 at 09:50
  • 1
    Might be a duplicate to http://stackoverflow.com/questions/4942023/set-default-schema-for-a-sql-query – Claude Jan 25 '17 at 09:50
  • Yes, imagine my SQL code has different parts that work with tables/procedures from different schemas. It would be logical to set current schema (as ORACLE allows) and use short names after that. Later on I may want to change the current schema and use short names again. – Buran Jan 25 '17 at 09:56
  • @Claude, thanks for the head up. That question is not exactly the same and it is rather old, situation may have changed since then. – Buran Jan 25 '17 at 10:01
  • It hasn't. The way schemas work has remained the same since the 2005 change. A user has a default schema; there is no notion of a schema for a connection. The use of schemas in SQL Server should be carefully planned. When using objects in a database with multiple schemas, always fully qualify names. Preferably, design applications so they do all their work in one schema. – Jeroen Mostert Jan 26 '17 at 10:27
  • @JeroenMostert, your comment looks like an answer. – Buran Jan 26 '17 at 12:31
  • And your question looks like a duplicate, if I leave out the stuff about asking for a rationale (which is opinion based). Don't worry, I won't tell if you won't. :-P – Jeroen Mostert Jan 26 '17 at 12:40

0 Answers0