set @counter = @counter + 1 /* increment counter */  /* I see this or slight variations of it all the time */
--------------------...
/* not quite...

The easiest and cleanest way to do this would be to create stored proc in the master db, start the name with "sp_", mark it as a system object, and...

SELECT tn.*
FROM (
    SELECT NUMBER
    FROM dbo.table_name
    GROUP BY NUMBER
    HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER...

In terms of potential lost performance and wasted resources, this is probably the single dumbest thing:

CREATE TABLE dbo.every_table_created
(
    id int IDENTITY(1, 1)...

Perhaps a 3rd option.  Rather than pulling the data from all instances, have each instance gather that info on itself and push it to a central location.