Link to home
Start Free TrialLog in
Avatar of jimmylew52
jimmylew52Flag for United States of America

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__2DB1C7EE' 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?
SOLUTION
Avatar of MohammedU
MohammedU
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Raja Jegan R
As those columns are added by SQL Server for replication, you have to first delete the replication activity, remove the publishing activity for this database and then try issuing the drop column statement given below

alter table tablename drop column columnname

This should help you out.
Avatar of jimmylew52

ASKER

MohammedU:

What might the default <defaultname> be or how do I find it

rrjegan17:

replication has already been deleted.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All publications and subscriptios have been deleted. Ran your script

sp_mergesubscription_cleanup [ @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__336AA144' 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.
Hope servername is the sysname and more details below:

http://msdn.microsoft.com/en-us/library/ms188391(SQL.90).aspx
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?