Find the template that is applied to a site collection or a subsite

We can find the template ID that is chosen while creating a site collection or a site from the database.  Its easy to find the template once we have the ID. The ID is stored in the Webs table of the content db.  The ID is just a numberic value like 3, 4, 50 etc.  Now we need to convert the ID to the actual name of the template.  This mapping is stored in couple of XML files based on whether the template is part of WSS or MOSS.  The XML files are stored here - C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\1033\XML.  The WSS templates are referenced in the WEBTEMP.XML and for MOSS, it is in the WEBTEMPSPS.XML.  If there is a custom site definition, you can modify any of these two files and also can create a new one.

Also check this blog for the Out of the box templates that is present in MOSS - https://blogs.msdn.com/dwinter/archive/2006/07/07/659613.aspx