Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
Ryan
Create an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>
|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>
sql

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hi All,
>> If I delete a field in SQL Server, will all constraints, foreign keys,
>> indices etc. associated with that field also be removed?
>> Thanks in advance
>> Ryan
>>
>

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>