Dropping Replicated Columns For SQL Server 2000

I'm sure all of us have tried, at some point, to delete a column from a table on a replicated database in Enterprise Manager only to be greeted with an error message:

Error 5074: The object 'DF__SomeTable__somec__71CF15EB' is dependant on column 'somecolumn'.
ALTER TABLE DROP COLUMN somecolumn failed because one or more objects access this column.

So you figure, I'll just delete the constraint and everything will be OK. But wait, that constraint is on every single replicated server, so you'll have to delete it there too. If there is a lot of latency between you and the other node, it is like waiting for a Christmas that will never come. Oh yeah, spoiler alert: Added bonus - Each one is named slightly differently. So while you may delete it from your main server, none of the syncs are happening or at least the snapshots are failing because you're missing a constraint that everyone has but you. If only there were some way to programaticly determine the randomly-named constraint and delete it. That's where I was today, so I hacked this together for Microsoft SQL Server 2000:

Use YourDatabaseHere
GO

-- Ad-hoc queries require this because sysobjects is just that important.
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
GO

DECLARE @parent_id INT
DECLARE @table_name NVARCHAR(255)
DECLARE @column_name NVARCHAR(255)

SELECT @table_name = 'SomeTable'
SELECT @column_name = 'somecolumn'
SELECT @parent_id = (SELECT sysobjects.id FROM sysobjects WHERE sysobjects.name = @table_name AND parent_obj = 0)

DELETE FROM sysobjects
WHERE parent_obj = @parent_id
AND (SELECT syscolumns.name FROM syscolumns WHERE syscolumns.id = @parent_id
AND syscolumns.colid = (SELECT sysconstraints.colid FROM sysconstraints
WHERE sysconstraints.id = @parent_id AND sysconstraints.constid = sysobjects.id)) = @column_name
AND xtype = 'D'
GO

-- Turn it off when we're done.
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
GO

If you are not familiar with system tables, I heartily recommend you check them out and learn them. Very handy for doing tricky things like this and it will make the above script seem less like magic and will lessen the chances you break something.

For SQL Server 2000: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx

For SQL Server 2005: http://msdn2.microsoft.com/en-us/library/ms179932.aspx

If you ARE familiar with system tables, and you find an error in the above script, please let me know in the harshest way possible, so I wont forget (within reason).

Now to run this command on all subscriber databases... For more info on that, check out this link:
http://doc.ddart.net/mssql/sql2000/html/replsql/replimpl_5t9d.htm