Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
MichaelYou first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:
> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > Hi,
> > I have a table with a column:
> > rv smallint default 1 not null
> >
> > I want to drop it:
> > alter table myTable
> > drop column rv
> >
> > But I get an error:
> > Server: Msg 5074, Level 16, State 1, Line 1
> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> > Server: Msg 4922, Level 16, State 1, Line 1
> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> > column.
> >
> > The 'object' mentioned above is the default constraint.
> > How do I just drop the column (without having to know waht constraints it
> > has)?
> > Thanks
> > Michael
> >
>
>|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
> > You first have to drop the default constraint
> >
> > alter table myTable
> > drop constraint DF__ccAstPublica__rv__27C3E46E
> >
> > Then drop the cilumn
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > Hi,
> > > I have a table with a column:
> > > rv smallint default 1 not null
> > >
> > > I want to drop it:
> > > alter table myTable
> > > drop column rv
> > >
> > > But I get an error:
> > > Server: Msg 5074, Level 16, State 1, Line 1
> > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
'rv'.
> > > Server: Msg 4922, Level 16, State 1, Line 1
> > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
this
> > > column.
> > >
> > > The 'object' mentioned above is the default constraint.
> > > How do I just drop the column (without having to know waht constraints
it
> > > has)?
> > > Thanks
> > > Michael
> > >
> >
> >
> >|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
>> > 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
>> > this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints
>> > it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:
> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> > > You first have to drop the default constraint
> > >
> > > alter table myTable
> > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > >
> > > Then drop the cilumn
> > >
> > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > Hi,
> > > > I have a table with a column:
> > > > rv smallint default 1 not null
> > > >
> > > > I want to drop it:
> > > > alter table myTable
> > > > drop column rv
> > > >
> > > > But I get an error:
> > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> 'rv'.
> > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> this
> > > > column.
> > > >
> > > > The 'object' mentioned above is the default constraint.
> > > > How do I just drop the column (without having to know waht constraints
> it
> > > > has)?
> > > > Thanks
> > > > Michael
> > > >
> > >
> > >
> > >
>
>|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:
> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> >> > 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> >> > this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints
> >> > it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
> > Sure
> >
> > What you can do is query the sysconstraints table in your db
> > and join it to syscolumns
> >
> >
> > select s2.name, object_name(s1.constid)
> > from sysconstraints s1
> > inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id =s2.id
> > and s1.colid = s2.colid and s1.status = 133141)
> >
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > > Armand,
> > > Thanks. Yes, I know I have to drop the constraint but...
> > > This is in a script and I don't know the auto generated name of the
> > > constraint, so I can't readily drop it.
> > > Is there a way to find out the constraint name for the DEFAULT for
that
> > > column?
> > > That way, I could fid the name then frop the constraint
programmatically.
> > > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > > Thanks
> > > Michael
> > >
> > > "Armando Prato" wrote:
> > >
> > > > You first have to drop the default constraint
> > > >
> > > > alter table myTable
> > > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > > >
> > > > Then drop the cilumn
> > > >
> > > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > > Hi,
> > > > > I have a table with a column:
> > > > > rv smallint default 1 not null
> > > > >
> > > > > I want to drop it:
> > > > > alter table myTable
> > > > > drop column rv
> > > > >
> > > > > But I get an error:
> > > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> > 'rv'.
> > > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > > ALTER TABLE DROP COLUMN rv failed because one or more objects
access
> > this
> > > > > column.
> > > > >
> > > > > The 'object' mentioned above is the default constraint.
> > > > > How do I just drop the column (without having to know waht
constraints
> > it
> > > > > has)?
> > > > > Thanks
> > > > > Michael
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Tibor
Thanks for the helpful advice :).
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||Yes, DEFAULTs are handles like constraints in SQL Server. Where in ANSI SQL, they are just column
attributes. Just one of those things to get used to... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:0B2F4A00-65E5-4DF1-B2C6-86CA9C21C4D8@.microsoft.com...
> Tibor
> Thanks for the helpful advice :).
> Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
> not DEFAULTs. I think this particular aspect of SQL Server's design is not
> particularly helpful. To my eye, it's much clearer to define:
> myColumn smallint default 1
> than
> myColumn smallint,
> constraint MyTab_DF_MyColumn default 1 for MyColumn
> Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
> allow me to drop a column that has an impicit not null, but not one with an
> implicit DEFAULT.
> Ho hum...
> Bet this isn't 'fixed' in 2005...
> Regards
> Michael
> "Tibor Karaszi" wrote:
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> This is why you should name your constraint in the first place... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>> > Armand,
>> > Thanks. Yes, I know I have to drop the constraint but...
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> > Is there a way to find out the constraint name for the DEFAULT for that
>> > column?
>> > That way, I could fid the name then frop the constraint programmatically.
>> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
>> > Thanks
>> > Michael
>> >
>> > "Armando Prato" wrote:
>> >
>> >> You first have to drop the default constraint
>> >>
>> >> alter table myTable
>> >> drop constraint DF__ccAstPublica__rv__27C3E46E
>> >>
>> >> Then drop the cilumn
>> >>
>> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> >> > Hi,
>> >> > I have a table with a column:
>> >> > rv smallint default 1 not null
>> >> >
>> >> > I want to drop it:
>> >> > alter table myTable
>> >> > drop column rv
>> >> >
>> >> > But I get an error:
>> >> > Server: Msg 5074, Level 16, State 1, Line 1
>> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> >> > Server: Msg 4922, Level 16, State 1, Line 1
>> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> >> > column.
>> >> >
>> >> > The 'object' mentioned above is the default constraint.
>> >> > How do I just drop the column (without having to know waht constraints it
>> >> > has)?
>> >> > Thanks
>> >> > Michael
>> >> >
>> >>
>> >>
>> >>
>>
.