Disable Replication, remove rowguide-column?

I've notice that when I disable replication the rowguide column remains on
each table.
Is there an easy way to remove it?
Robert,
you'll need to do this manually. This is by design as your T-SQL code may
refer to the column, either directly or indirectly, so until the dependency
is removed, the column has to remain.
Regards,
Paul Ibison
|||You will have to do a Alter table drop column, You can create a script to
make this easier.
thanks
gopal
|||here is something. It will destroy all active publications and
subscriptions, make sure you drop them before running this.
exec sp_configure N'allow updates', 1
go
reconfigure with override
go
DECLARE @.name varchar(129)
DECLARE @.username varchar(129)
DECLARE @.insname varchar(129)
DECLARE @.delname varchar(129)
DECLARE @.updname varchar(129)
set @.insname=''
set @.updname=''
set @.delname=''
DECLARE list_triggers CURSOR FOR
select distinct replace(artid,'-',''), sysusers.name from
sysmergearticles,sysobjects, sysusers where
sysmergearticles.objid=sysobjects.id
and sysusers.uid=sysobjects.uid
OPEN list_triggers
FETCH NEXT FROM list_triggers INTO @.name, @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping trigger ins_' +@.name
select @.insname='drop trigger ' +@.username+'.ins_'+@.name
exec (@.insname)
PRINT 'dropping trigger upd_' +@.name
select @.updname='drop trigger ' +@.username+'.upd_'+@.name
exec (@.delname)
PRINT 'dropping trigger del_' +@.name
select @.delname='drop trigger ' +@.username+'.del_'+@.name
exec (@.updname)
FETCH NEXT FROM list_triggers INTO @.name, @.username
END
CLOSE list_triggers
DEALLOCATE list_triggers
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1) begin DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications
OPEN list_pubs
FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@.name
EXEC sp_dropsubscription @.publication=@.name, @.article='all', @.subscriber
='all'
EXEC sp_droppublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END
CLOSE list_pubs
DEALLOCATE list_pubs
end
GO
DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles
OPEN list_replicated_tables
FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END
CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables
GO
UPDATE syscolumns set colstat = colstat & ~4096 WHERE colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO
DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name like 'syncobj_%' or
name
like 'ctsv_%' or name like 'tsvw_%' or name like 'ms_bi%')
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
GO
DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name like 'sp_ins_%' or
name
like 'sp_MSdel_%' or name like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
GO
DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name like '_onflict%'
OPEN list_conflict_tables
FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END
CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables
GO
UPDATE syscolumns set colstat=2 WHERE name='rowguid'
GO
Declare @.name nvarchar(200), @.constraint nvarchar(200)
DECLARE list_rowguid_constraints CURSOR FOR
select sysusers.name+'.'+object_name(sysobjects.parent_ob j), sysobjects.name
from sysobjects, syscolumns,sysusers where sysobjects.type ='d' and
syscolumns.id=sysobjects.parent_obj
and sysusers.uid=sysobjects.uid
and syscolumns.name='rowguid'
OPEN list_rowguid_constraints
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint WHILE
@.@.FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name) + ' DROP CONSTRAINT '
+@.constraint
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint END
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select sysusers.name+'.'+object_name(sysindexes.id), sysindexes.name from
sysindexes, sysobjects,sysusers where sysindexes.name like 'index%' and
sysobjects.id=sysindexes.id and sysusers.uid=sysobjects.uid
OPEN list_rowguid_indexes
FETCH NEXT FROM list_rowguid_indexes INTO @.name, @.constraint WHILE
@.@.FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name, @.constraint END
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_ms_bidi_tables CURSOR FOR
select sysusers.name+'.'+sysobjects.name from
sysobjects,sysusers where sysobjects.name like 'ms_bi%'
and sysusers.uid=sysobjects.uid
and sysobjects.type='u'
OPEN list_ms_bidi_tables
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping ms_bidi ' +@.name
select @.name='drop table ' + rtrim(@.name )
EXEC sp_executesql @.name
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
END
CLOSE list_ms_bidi_tables
DEALLOCATE list_ms_bidi_tables
GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + ' drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
Declare @.name nvarchar(129)
DECLARE list_views CURSOR FOR
select name From sysobjects where type ='v' and status =-1073741824 and name
<>'sysmergeextendedarticlesview'
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication views ' +@.name
select @.name='drop view ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
go
Declare @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
select name From sysobjects where type ='p' and status = -536870912
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication procedure ' +@.name
select @.name='drop procedure ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergepublications]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergepublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM syssubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticleupdates]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysarticleupdates
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[systranschemas]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DELETE FROM systranschemas
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergearticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergearticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
DELETE FROM sysarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DELETE FROM syspublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemachange]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemachange
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubsetfilters]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubsetfilters
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotjobs]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotjobs
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotviews]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotviews
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_altsyncpartners]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_altsyncpartners
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_contents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_contents
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_delete_conflicts]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_delete_conflicts
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_errorlineage]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_errorlineage
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_genhistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_genhistory
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_replinfo]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_replinfo
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_tombstone]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_tombstone
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSpub_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSpub_identity_range
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSrepl_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSrepl_identity_range
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSreplication_subscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSreplication_subscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSsubscription_agents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSsubscription_agents
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
create table syssubscriptions (artid int, srvid smallint, dest_db sysname,
status tinyint, sync_type tinyint, login_name sysname, subscription_type
int, distribution_jobid binary, timestamp timestamp,update_mode tinyint,
loopback_detection tinyint, queued_reinit bit)
CREATE TABLE [dbo].[syspublications] (
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[pubid] [int] IDENTITY (1, 1) NOT NULL ,
[repl_freq] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_method] [tinyint] NOT NULL ,
[snapshot_jobid] [binary] (16) NULL ,
[independent_agent] [bit] NOT NULL ,
[immediate_sync] [bit] NOT NULL ,
[enabled_for_internet] [bit] NOT NULL ,
[allow_push] [bit] NOT NULL ,
[allow_pull] [bit] NOT NULL ,
[allow_anonymous] [bit] NOT NULL ,
[immediate_sync_ready] [bit] NOT NULL ,
[allow_sync_tran] [bit] NOT NULL ,
[autogen_sync_procs] [bit] NOT NULL ,
[retention] [int] NULL ,
[allow_queued_tran] [bit] NOT NULL ,
[snapshot_in_defaultfolder] [bit] NOT NULL ,
[alt_snapshot_folder] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[pre_snapshot_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[post_snapshot_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[compress_snapshot] [bit] NOT NULL ,
[ftp_address] [sysname] NULL ,
[ftp_port] [int] NOT NULL ,
[ftp_subdirectory] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ftp_login] [sysname] NULL ,
[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[allow_dts] [bit] NOT NULL ,
[allow_subscription_copy] [bit] NOT NULL ,
[centralized_conflicts] [bit] NULL ,
[conflict_retention] [int] NULL ,
[conflict_policy] [int] NULL ,
[queue_type] [int] NULL ,
[ad_guidname] [sysname] NULL ,
[backward_comp_level] [int] NOT NULL
) ON [PRIMARY]
GO
create view sysextendedarticlesview
as
SELECT *
FROM sysarticles
UNION ALL
SELECT artid, NULL, creation_script, NULL, description, dest_object,
NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL, type,
NULL,
schema_option, dest_owner
FROM sysschemaarticles go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[sysarticles]
GO
CREATE TABLE [dbo].[sysarticles] (
[artid] [int] IDENTITY (1, 1) NOT NULL ,
[columns] [varbinary] (32) NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_table] [sysname] NOT NULL ,
[filter] [int] NOT NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_objid] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sysschemaarticles]
GO
CREATE TABLE [dbo].[sysschemaarticles] (
[artid] [int] NOT NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_object] [sysname] NOT NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY]
GO
declare @.dbname varchar(130)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''merge publish'',''false'''
exec (@.dbname)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''publish'',''fals e'''
exec (@.dbname)
reconfigure with override
go
select db_name()
Hilary
973 254-8140
732 687-2264 (cell)
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:#YDThPpEEHA.3976@.TK2MSFTNGP12.phx.gbl...
> I've notice that when I disable replication the rowguide column remains on
> each table.
> Is there an easy way to remove it?
>
|||Very impressive! - like sp_removedbreplication but a bit more comprehensive.
Regards,
Paul
|||this sp not clear the column guids... ;-)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje
news:e8Qb2JqEEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Very impressive! - like sp_removedbreplication but a bit more
comprehensive.
> Regards,
> Paul
>
|||The script has a cursor to do this:
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + ' drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
I guess it's limited in the sense that the GUID column could be called
something else and it assumes merge replication, but that apart, it should
work.
Regards,
Paul Ibison