Post reply

Changing the Current Schema on the FLY

  • May 30, 2007 at 4:13 pm

    #196053

    Doesnt anyone know if theres a way to change the current schema on the fly.

    For instance I dont want to have to prepend all the tables in my query with the fully qualified name. I wanna be able to issue a statement like "SET SCHEMA schema_name" (as in Oracle) and then that becomes my current shchema.

  • May 30, 2007 at 8:46 pm

    #709891

    Yes, this can be done through CREATE/ALTER USER statement by assigning / re-assigning a default schema to a user. Don't need to prefix the object name with the user default schema name.

    ALTER USER username WITH DEFAULT_SCHEMA = schemaname;

    Permission required: ALTER on the user. BOL says that a user can change its own schema, which means that a user can alter his/her own login.

     

  • May 31, 2007 at 12:21 am

    #709915

    For instance I dont want to have to prepend all the tables in my query with the fully qualified name.

    Why not? Unqualified naming can lead to slower queries and unnecessary recompiles

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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