crompnk
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER