It seems that I was thinking too hard when I attempted to understand how "Apply to All Modules" might be implemented.  Well I was all wrong and have been living under a misconception for quite some time.  There were clues that my conceptualization was incorrect, but I ignored them because I was right -- and it must be something else ... yes -- pure ignorance.

OK, so how is this feature implemented and what are some of the issues associated with it? 

First of all where is the option?  When you place a module on a page and visit the modules settings, you will find the "Display Module on All Pages" option under the Advanced Settings - you will need to expand the section as it is typically collapsed.

I have expanded the "help" text ... which to me does not truly explain what will happen if this option is enabled.  So what does happen? 

When enabled, the current module will be added to all existing pages in the same panel position. 

When you update date the page, the module controller update will see this option is TRUE, and will then create a new module reference for each page that exists in your site at the moment.  If any new pages are later, you will need to manually add the module !!!

[EDIT: Mitch Sellers pointed out that the module WILL be added to new pages added to the site. Also If the module is added, then how.  The Modules tables contains "AllTabs" and this is used to add the module to new pages.  Thanks Mitch for catching the error. ]

So how can you tell if you have such a module on your site?  You  can't.  The flag indicating you wanted the module to be applied to all pages is lost. If you check the Modules table, you will NOT find any indicator for "Add to All Pages".  So how is this performed?

Within the DNN database, there exists a table (TabModules - remember that Tabs = Pages) that relates what modules are on what page.  Typically you can think of this as a one to one relationship -- I placed an instance of module X on page Y, so this table will have a single row containing X points Y.  However, the table also supports one to many relationships.  So the implementation of Display Module on All Pages is implemented by creating mulitple entries in this table for the single module and each page (tab).  Here is the code snippet that implements the one to many relationship.

' apply settings to all desktop modules in portal
If objModule.AllModules Then
    Dim objTabs As New TabController
    For Each tabPair As KeyValuePair(Of Integer, TabInfo) In objTabs.GetTabsByPortal(objModule.PortalID)
        Dim objTab As TabInfo = tabPair.Value
        If Not objTab.IsAdminTab Then
            For Each modulePair As KeyValuePair(Of Integer, ModuleInfo) In GetTabModules(objTab.TabID)
                Dim objTargetModule As ModuleInfo = modulePair.Value
                DataProvider.Instance().UpdateTabModule(objTargetModule.TabID, objTargetModule.ModuleID,
                    objTargetModule.ModuleOrder, objTargetModule.PaneName, objTargetModule.CacheTime, _
                    objModule.Alignment, objModule.Color, objModule.Border, objModule.IconFile, _
                    objModule.Visibility, objModule.ContainerSrc, objModule.DisplayTitle, _
                    objModule.DisplayPrint, objModule.DisplaySyndicate)
            Next
        End If
    Next
End If
 
The main point here is the "Display on All Pages" option is done at module add time, not at page display time.  You will also note that "All Pages" does NOT include Admin Pages.

The pre-loading does have it's good points.  If you do not want "All Pages" just "Most" pages.  You can delete the module from those pages where the module is unwanted and not have to worry about it "re-appearing". 

So how can we locate these one module to many page occurances?

select 
    T.ModuleID, 
    M.ModuleTitle,
    Count(T.TabID) as OnPages
from 
    TabModules T
    join Modules M on M.ModuleID = T.ModuleID
group by 
    T.ModuleID,
    M.ModuleTitle
having
    Count(T.TabID) > 1
order by
    OnPages desc
 

On the Tressleworks site, the result is something like:

ModuleID    ModuleTitle                    OnPages
----------- ------------------------------ -------
445         Google Add Panel               65
489         Google Add Panel               27
417         Other Examples                 9
633         ChartLinks                     7
502         other samples                  6
427         MenuLinks                      3
421         Text/HTML                      3
415         Stored Procedure               2
416         SQLGridSelectedView - Example  2
431         MenuLinks                      2
432         MenuLinks                      2
419         SQLGridSelectedView - Example  2
492         MenuLinks                      2
494         Text/HTML                      2

As you can see, the on Page Count for the Google Add Panel has a very high occurance,   In fact I did it twice ...    Othe features of DNN take advantage of the one to many modules to pages and you can see this with the Menu Links module in the above results.

Hope this helps others understand -- I had it wrong and made several mistakes because of it.

Paul.