Get me some articles!

So, as I alluded to here, I was doing some work today. Specifically, I was adding articles to a publication. I had an idea that I should be able to come up with a way to add the articles that are missing from the publication. That is, if I have a database with 20 tables and 17 are already articles, then the script should be smart enough to add the 3 that aren't.

declare tablez cursor for
    select schema_name([schema_id]), t.name 
    from sys.tables t
    left join sysarticles a
       on t.[object_id] = a.[objid] 
    where OBJECTPROPERTY([object_id], 'IsMSShipped') = 0  --no system objects
       and OBJECTPROPERTY([object_id], 'TableHasPrimaryKey') = 1 --only give me tables that can be replicated, please
       and a.[objid] is null --not already an article
open tablez
declare @schema sysname, @table sysname, @fullname sysname
while (1=1)
    fetch next from tablez into @schema, @table
    if (@@FETCH_STATUS <> 0)
    set @fullname = @schema + '.' + @table
    exec sp_addarticle
    @publication = 'Publication',
        @article = @fullname,
        @source_object = @table,
        @destination_table = @table,
        @type = 'logbased',
        @destination_owner = @schema,
        @status = 16, 
        @source_owner = @schema

close tablez
deallocate tablez

The astute among you might wonder why I named the cursor "tablez". I'll present you with two options.
  1. "tables" highlights as a system table in SSMS (a la sys.tables).
  2. I'm l33t.

I leave it to the reader to choose. As always, the standard warning applies.

No comments:

Post a Comment