Post reply

Changing schema for tables

  • August 16, 2012 at 9:50 am

    #260208

    Hello,

    We have a custom schema for tables and want to change it to DBO for same tables due to some project requirements.

    we can still keep the schema but no objects assigned to schema.

    We have some code which is written as Select * from customschema.tablename.

    Can we change the logins default schema to CustomSchema and will the old code which was referring to customschema still work.

    Hope i am clear on this..

    Thanks In Advance!

  • August 16, 2012 at 11:03 am

    #1526730

    no, i don't believe it will work.

    if no schema is provided in SELECT columnList From Tablename, the default schema is assumed

    ...customschema in your example.

    if you explicitly states teh schemanmae, and has SELECT columnList From customschema.Tablename, then if the table does not exist, you'll get an error about invalud objectname.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'customschema.Tablename'.

    you'll need to do a find/replace in your code and recompile if you are going to move/eliminate the objects in the schema.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 16, 2012 at 11:13 am

    #1526734

    Thanks very much!

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

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