[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • Last Modified:

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

0
crompnk
Asked:
crompnk
1 Solution
 
crompnkAuthor Commented:
ok
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now