Getting the TCP port of all instances on a machine

So... we run multiple SQL instances per physical server. There also seems to be a firewall issue between my laptop and the environment that prevents port resolution for those instances. So, I decided to whip up a little Powershell script.

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)
begin
    fetch next from tablez into @schema, @table
    if (@@FETCH_STATUS <> 0)
        break
    set @fullname = @schema + '.' + @table
    exec sp_addarticle
    @publication = 'Publication',
        @article = @fullname,
        @source_object = @table,
        @destination_table = @table,
        @type = 'logbased',
        --@schema_option
        @destination_owner = @schema,
        @status = 16, 
        @source_owner = @schema

end
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.

Small cursor usage trick

Okay... everyone knows that cursors are bad. And yet we all use them from time to time. Historically I've written them like this:

declare @table sysname 
declare curs cursor for
select name from sys.tables
fetch next from curs into @table

while (@@fetch_stats = 0)
begin
  --do stuff
  fetch next from curs into @table
end

close curs
deallocate curs

While writing some code today, I had this insight:

declare @table sysname 
declare curs cursor for
select name from sys.tables

open curs

while (1=1)
begin
  fetch next from curs into @table
  if (@@fetch_status <> 0)
    break
  --do stuff
end
close curs
deallocate curs

With the advantage that I only have to maintain one fetch line as I'm inevitably adding and removing columns from the cursor. I'm sure that I'm not the first to come up with this, but hey... I thought it was nice.