Re: Changing Default Names

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dave (anonymous_at_discussions.microsoft.com)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 08:38:05 -0800

A table owns the constrains..........Defaults...

ALTER TABLE [differentowner].[mytable] WITH NOCHECK ADD
        CONSTRAINT [DF__mytable__col1__834637] DEFAULT (0)
FOR [col1]
GO

I ran the sp_rename the way he specified and I got:

Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
Line 223
Either the parameter @objname is ambiguous or the claimed
@objtype (object) is wrong.

>-----Original Message-----
>But who owns the constraints? If the constraint has a
different owner, you
>must specify the owner name when referencing the object.
>
>--
>HTH
>----------------
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>
>"Dave" <anonymous@discussions.microsoft.com> wrote in
message
>news:1768501c418b6$97e79e70$a401280a@phx.gbl...
>> I am connecting as sysadmin.
>>
>>
>>
>> >-----Original Message-----
>> >Hi Dave
>> >
>> >Is it possible the constraint has a different owner
than
>> the user you are
>> >currently connected as?
>> >
>> >--
>> >HTH
>> >----------------
>> >Kalen Delaney
>> >SQL Server MVP
>> >www.SolidQualityLearning.com
>> >
>> >
>> >"Dave" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:1725d01c41833$5dffcb20$a401280a@phx.gbl...
>> >> NO. Here is what I am running:
>> >>
>> >>
>>
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
>> >> i_df', 'object'
>> >>
>> >>
>> >> Here is what I get:
>> >>
>> >> Server: Msg 15248, Level 11, State 1, Procedure
>> sp_rename,
>> >> Line 223
>> >> Either the parameter @objname is ambiguous or the
>> claimed
>> >> @objtype (object) is wrong.
>> >>
>> >>
>> >>
>> >> >-----Original Message-----
>> >> >sorry, that was a CHECK constraint... here's a
default:
>> >> >
>> >> >
>> >> >create table a(id int)
>> >> >GO
>> >> >
>> >> >create table b (id int)
>> >> >GO
>> >> >
>> >> >alter table b add constraint r_b default (1) for id
>> >> >GO
>> >> >
>> >> >sp_rename 'r_b', 'r_c', 'object'
>> >> >GO
>> >> >
>> >> >"Adam Machanic" <amachanic@air-
>> >> worldwide.nospamallowed.com> wrote in message
>> >> >news:#oIt9BDGEHA.4044@TK2MSFTNGP10.phx.gbl...
>> >> >> It works for me... Does the following work for
you:
>> >> >>
>> >> >>
>> >> >> create table a(id int)
>> >> >> GO
>> >> >>
>> >> >> create table b (id int)
>> >> >> GO
>> >> >>
>> >> >> alter table b add constraint r_b check (id = 1)
>> >> >> GO
>> >> >>
>> >> >> sp_rename 'r_b', 'r_c', 'object'
>> >> >> GO
>> >> >>
>> >> >> "Dave" <anonymous@discussions.microsoft.com>
wrote in
>> >> message
>> >> >> news:1722a01c4182f$5d987aa0$a401280a@phx.gbl...
>> >> >> > I can not use sp_rename because the defaults are
>> not
>> >> >> > recognized as objects.
>> >> >> >
>> >> >> >
>> >> >> > >-----Original Message-----
>> >> >> > >Hard to say if there are side effects; it's not
>> the
>> >> >> > supported way of
>> >> >> > >renaming objects. Instead, use sp_rename.
Look
>> up
>> >> >> > syntax in BOL.
>> >> >> > >
>> >> >> > >
>> >> >> > >"Dave" <anonymous@discussions.microsoft.com>
>> wrote in
>> >> >> > message
>> >> >> > >news:1444801c4182b$45f41160$a601280a@phx.gbl...
>> >> >> > >> I am planning to change the default names
from
>> >> >> > >>
>> >> >> > >> 'DF__tablename__columnname_28F7FFC9'to
>> >> >> > >> 'tablename_columnname_df'.
>> >> >> > >>
>> >> >> > >> Depending on the number of default
columns 'df1,
>> >> df2,
>> >> >> > >> df3 .....e.t.c' will be added.
>> >> >> > >>
>> >> >> > >> I am going to rename them in the 'sysobjects'
>> table
>> >> >> > where
>> >> >> > >> I see the only location for them.
>> >> >> > >>
>> >> >> > >> Is there any side effects to this ?.
>> >> >> > >>
>> >> >> > >> Thanks for any help.
>> >> >> > >
>> >> >> > >
>> >> >> > >.
>> >> >> > >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>