[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Drop SQL Server 2005 replication columns

Posted on 2010-08-26
2
Medium Priority
?
922 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

656 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