Solved

Drop SQL Server 2005 replication columns

Posted on 2010-08-26
2
880 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
ID: 33531384
0
 

Author Closing Comment

by:crompnk
ID: 33575517
ok
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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