Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: Problem renaming default constraints in schema other than dbo

    Does anyone know how to tell sp_rename to look in a schema other than the default.
    The code below reproduces the problem.

    -- WORKS IN default schema
    --
    create table dbo.TestDF1(
    dfField int constraint DF1 default 0
    )
    go

    sp_rename 'DF1', 'DF2', 'OBJECT'
    go

    Select name
    From
    sys.default_constraints
    where
    object_name(parent_object_id) = 'TestDF1'
    go

    drop table dbo.TestDF1
    go

    -- DOESN'T WORK IN added schema
    --
    create schema TestSchema
    go

    create table TestSchema.TestDF2(
    dfField int constraint DF3 default 0
    )
    go

    sp_rename 'DF3', 'DF4', 'OBJECT'
    go

    Msg 15248, Level 11, State 1, Procedure sp_rename, Line 315
    Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
    ---------------------------
    drop table TestSchema.TestDF2
    go

    drop schema TestSchema
    go

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about:
    Code:
    sp_rename 'testschema.DF3', 'testschema.DF4', 'OBJECT'

  3. #3
    Join Date
    Dec 2007
    Posts
    2

    Smile

    That's got it. I had tried:

    sp_rename 'testschema.TestDF2.DF3', 'DF4', 'OBJECT'

    but I see now that I don't need the table scope. Also this works too:

    sp_rename 'testschema.DF3', 'DF4', 'OBJECT'


    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •