How can i check if an object is still in replication

Question

How can i check if an object is still in replication

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
July 28, 2009 at 5:22 am

#75507

Hi,
I have disabled replication on a SQL installation, but I have a feeling there are still come reminants from replication left on the database.
Is there a command which can be executed on the database to check if an object is marked for replication, I currently dont have the distribution database.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

I have disabled replication on a SQL installation, but I have a feeling there are still come reminants from replication left on the database.

Did you disable replication or removed replication completely.

Is there a command which can be executed on the database to check if an object is marked for replication, I currently dont have the distribution database.

Which version of sql server you are using and what is the servicepack and type of replication you are using.

If you are using snapshot or transactional replication you can get the list of articles which are in replication. This has to be executed at the publisher in publisher db. works with 2000 with sp3 or above.

use publisherdb

sp_helparticle @publication='publicationname'

[font="Verdana"]Thanks
Chandra Mohan[/font]

chris.schau Mr or Mrs. 500 Points: 580 More actions · Answer 2

I run into this every now and then when I restore a database (that was a subscriber or publisher at the time of backup) to a new instance. I find that the best way to remove replication information from a database is:

USE [DatabaseName]

EXEC sp_removedbreplication

GO

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 3

Chandu (7/28/2009)
I have disabled replication on a SQL installation, but I have a feeling there are still come reminants from replication left on the database.
Did you disable replication or removed replication completely.
Is there a command which can be executed on the database to check if an object is marked for replication, I currently dont have the distribution database.
Which version of sql server you are using and what is the servicepack and type of replication you are using.
If you are using snapshot or transactional replication you can get the list of articles which are in replication. This has to be executed at the publisher in publisher db. works with 2000 with sp3 or above.
use publisherdb
sp_helparticle @publication='publicationname'

SQL version is SQL 2000 and SP4.

Replication removed, but I still have a feeling that there are some elements elft of it.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

here possibly could be stored procedures or system elements left, but if you have removed replication, that doesn't matter. It won't impact your server instance.

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 5

Steve Jones - Editor (7/28/2009)
here possibly could be stored procedures or system elements left, but if you have removed replication, that doesn't matter. It won't impact your server instance.

Thanks.

Do you have a script the tells if an object is marked for replication ?

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

From BOL http://msdn.microsoft.com/en-us/library/ms151797.aspx

--Which objects in the database are published?

select name as published_object, schema_id, is_published as is_tran_published, is_merge_published, is_schema_published

from sys.tables where is_published = 1 or is_merge_published = 1 or is_schema_published = 1

union

select name as published_object, schema_id, 0, 0, is_schema_published

from sys.procedures where is_schema_published = 1

union

select name as published_object, schema_id, 0, 0, is_schema_published

from sys.views where is_schema_published = 1

sys.objects also talks about if something is published.

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 7

Steve Jones - Editor (7/28/2009)
From BOL http://msdn.microsoft.com/en-us/library/ms151797.aspx
--Which objects in the database are published?
select name as published_object, schema_id, is_published as is_tran_published, is_merge_published, is_schema_published
from sys.tables where is_published = 1 or is_merge_published = 1 or is_schema_published = 1
union
select name as published_object, schema_id, 0, 0, is_schema_published
from sys.procedures where is_schema_published = 1
union
select name as published_object, schema_id, 0, 0, is_schema_published
from sys.views where is_schema_published = 1
sys.objects also talks about if something is published.

Thanks for the reply, but the database is a SQL 2000 and it does not have the sys schema

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

MANU-J.

SSC-Dedicated

Points: 31126

July 31, 2009 at 6:21 pm

#1033222

Try:

select * from sysobjects

where replinfo0

MJ