jimmylew52
asked on
Need to drop a column in table sql 2000
when building a merge replication a column was added to all of my tables. I need to remove this column. I have run the following in querry anaalyzer
alter table tablename
drop column rowguid
I get the following error
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__FAHead__rowgui__2DB1C 7EE' is dependent on column 'rowguid'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'index_165575628' is dependent on column 'rowguid'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.
This column must be remover ASAP. I have tried to remove it by right clicking and selecting delete but delete is not available.
How do I delete this column?
alter table tablename
drop column rowguid
I get the following error
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__FAHead__rowgui__2DB1C
Server: Msg 5074, Level 16, State 1, Line 1
The index 'index_165575628' is dependent on column 'rowguid'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.
This column must be remover ASAP. I have tried to remove it by right clicking and selecting delete but delete is not available.
How do I delete this column?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MohammedU:
What might the default <defaultname> be or how do I find it
rrjegan17:
replication has already been deleted.
What might the default <defaultname> be or how do I find it
rrjegan17:
replication has already been deleted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All publications and subscriptios have been deleted. Ran your script
sp_mergesubscription_clean up [ @publisher =] 'servername'
, [ @publisher_db =] 'T2Com'
, [ @publication =] 'T2Com'
received error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'servername'.
alter table FADetailArchive
drop column rowguid
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__FADetailA__rowgu__336 AA144' is dependent on column 'rowguid'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.
sp_mergesubscription_clean
, [ @publisher_db =] 'T2Com'
, [ @publication =] 'T2Com'
received error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'servername'.
alter table FADetailArchive
drop column rowguid
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__FADetailA__rowgu__336
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.
Hope servername is the sysname and more details below:
http://msdn.microsoft.com/en-us/library/ms188391(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms188391(SQL.90).aspx
ASKER
Wasn't what I needed. Here is what is working.
sp_HelpConstraint tablename
alter table Tablename drop constraint (enter constraint for rowguid Here - from previous command)
alter table Conversation drop column rowguid
Get an error stating an Index is dependant
drop index Conversation.(enter index from error message above)
alter table Conversation drop column rowguid
This works but hopefully it can be scripted as I have to run this several hundred times;
I am not good at sql scripting. any ideas on how to do this?
sp_HelpConstraint tablename
alter table Tablename drop constraint (enter constraint for rowguid Here - from previous command)
alter table Conversation drop column rowguid
Get an error stating an Index is dependant
drop index Conversation.(enter index from error message above)
alter table Conversation drop column rowguid
This works but hopefully it can be scripted as I have to run this several hundred times;
I am not good at sql scripting. any ideas on how to do this?
alter table tablename drop column columnname
This should help you out.