Solved

Drop SQL Server 2005 replication columns

Posted on 2010-08-26
2
850 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:crompnk
2 Comments
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
Comment Utility
0
 

Author Closing Comment

by:crompnk
Comment Utility
ok
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now