Drop SQL Server 2005 replication columns

Hi,

I have a database in SQL Server 2005, which has the column 'rowguid' in each table. This is due to replication.

I need to delete all the rowguid columns from all tables and associated defaults.
Below is the code I am using, but I get the following error:

Msg 5074, Level 16, State 1, Line 1
The index 'index_5575058' is dependent on column 'rowguid'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.

Thank you
/**************************************************************/
--DROP REPLICATION
/**************************************************************/

-- Clear All Defaults
DECLARE defaults CURSOR FOR
SELECT name,parent_obj FROM sysobjects WHERE name like 'DF%' and name like '%_rowgu_%'

OPEN defaults
DECLARE @Name varchar(1000)
DECLARE @TableName varchar(1000)
DECLARE @ParentObj int

FETCH NEXT FROM defaults into @Name,@ParentObj
WHILE @@FETCH_STATUS <> -1
BEGIN
	SELECT @TableName = (SELECT name FROM sysobjects WHERE id=@ParentObj)
	exec('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @Name + ']')
	FETCH NEXT FROM defaults into @Name,@ParentObj
END

CLOSE defaults
DEALLOCATE DEFAULTS

GO 

--now clear all tables
DECLARE cols CURSOR FOR
SELECT name,id FROM syscolumns WHERE PATINDEX('rowguid%',name)>0 AND id IN(SELECT id FROM sysobjects WHERE xtype='U')

OPEN cols
DECLARE @Name varchar(1000)
DECLARE @TableName varchar(1000)
DECLARE @ParentObj int

FETCH NEXT FROM cols into @Name,@ParentObj
WHILE @@FETCH_STATUS <> -1
BEGIN
	SELECT @TableName = (SELECT name FROM sysobjects WHERE id=@ParentObj)
	exec('ALTER TABLE [' + @TableName + '] DROP COLUMN [' + @Name + ']')
	FETCH NEXT FROM cols into @Name,@ParentObj
END

CLOSE cols
DEALLOCATE cols

GO

Open in new window

crompnkAsked:
Who is Participating?
 
crompnkAuthor Commented:
ok
0
All Courses

From novice to tech pro — start learning today.