{"id":3059,"date":"2010-05-17T02:06:00","date_gmt":"2010-05-17T02:06:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/is-this-table-replicated\/"},"modified":"2016-07-28T10:50:00","modified_gmt":"2016-07-28T10:50:00","slug":"is-this-table-replicated","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/is-this-table-replicated\/","title":{"rendered":"Is this table replicated?"},"content":{"rendered":"<p>Another in the potentially quite sporadic series of <strong>I need to do &#8230; but I cant find it on the internet<\/strong>.     <br \/>I have a table that I think might be involved in replication but I don&#8217;t know which publication its in&#8230;     <br \/>We know the table name &#8211; &#8216;MyTable&#8217;     <br \/>We have replication running on our server and its replicating our database, or part of it &#8211; &#8216;MyDatabase&#8217;.     <br \/>We need to know if the table is replicated and if so which publication is going to need to be reviewed if we make changes to the table.     <\/p>\n<p>How?<code>       <\/p>\n<p>USE MyDatabase       <br \/>GO        <br \/>\/* Lots of info about our table but not much that's relevant to our current requirements*\/       <br \/>SELECT * FROM sysobjects       <br \/>WHERE NAME = 'MyTable'       <br \/>-- mmmm, getting there       <br \/>\/* To quote BOL - \"Contains one row for each merge article defined in the local database. This table is stored in the publication database.replication\"        <br \/>interesting column is [pubid]        <br \/>*\/        <br \/>SELECT * FROM dbo.sysmergearticles AS s       <br \/>WHERE NAME = 'MyTable'       <br \/>-- really close now       <br \/>\/*        <br \/>the sysmergepublications table - Contains one row for each merge publication defined in the database. This table is stored in the publication and subscription databases.        <br \/>so this would be where we get the publication details        <br \/>*\/        <br \/>SELECT * FROM dbo.sysmergepublications AS s       <br \/>WHERE s.pubid = '2876BBD8-3D4E-4ED8-88F3-581A659E8144'       <br \/>-- DONE IT.       <br \/>\/*        <br \/>Combine the two tables above and we get the information we need        <br \/>*\/        <br \/>SELECT s.[name] AS [Publication name] FROM dbo.sysmergepublications AS s       <br \/>INNER JOIN dbo.sysmergearticles AS s2 ON s.pubid = s2.pubid       <br \/>WHERE s2.NAME = 'MyTable'       <br \/><\/code>    <br \/>So I now know which<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Another in the potentially quite sporadic series of I need to do &#8230; but I cant find it on the internet. I have a table that I think might be involved in replication but I don&#8217;t know which publication its in&#8230; We know the table name &#8211; &#8216;MyTable&#8217; We have replication running on our server&#8230;&hellip;<\/p>\n","protected":false},"author":101210,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-3059","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101210"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3059"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3059\/revisions"}],"predecessor-version":[{"id":25027,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3059\/revisions\/25027"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3059"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3059"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}