?
Solved

Drop SQL Server 2005 replication columns

Posted on 2010-08-26
2
Medium Priority
?
909 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Accepted Solution

by:
dwe761 earned 1500 total points
ID: 33531384
0
 

Author Closing Comment

by:crompnk
ID: 33575517
ok
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

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