Ever been in a deployment situation where the deployment script breaks due to a table being replicated? I have – many times. Hopefully this tidbit of information will help you in your future deployments.

When I receive a scheduled or even a non scheduled deployment I like to prep for it so I don’t have any surprises at the actual time of deployment. I use Red Gates SQL Compare tool for my deployments; whatever your mechanism is the methodology is similar.

Table A is being replicated from the publisher down to a reporting environment lets say; Table A has a modification to some part of the schema. If Table A is being replicated you will receive an error on deployment stating that the table is being “published” for replication.

What I like to do is review the schema before hand; I have a pretty good working knowledge of what I am replicating over but I also deal with 17 publications that house various amounts of tables and I might not be able to recall where all the tables are in what publications.

In order for me to figure this out quickly I use the following script. I do not have a use statement at the beginning; however I do run this in the specific database that I am deploying to:

SELECT sps.name
FROM syspublications sps WITH ( NOLOCK )
JOIN sysarticles sas WITH ( NOLOCK ) ON sps.pubid = sas.pubid
WHERE sas.name = ‘tablename’ –change to your table name

By running the above script it will allow you to find what publication the table is being replicated in. From there you can script out the drop and create statements for the publication and continue on with your deployment.