Share Useful SQL Queries

In this article I am going to share useful queries that i have used for troubleshooting DotNetNuke problems. You are also welcome to share your queries to help DotNetNuke Users.

1) If you want to remove unused modules from a DotNetNuke portal that has many pages you may use following query (Browse Host > SQL and paste this query at textbox and click to Execute button).

SELECT
    DM.ModuleName,
    DM.FriendlyName,
    (SELECT COUNT(*) 
     FROM Modules 
       WHERE ModuleDefId = MD.ModuleDefId
    ) AS 'TotalInstances',
    (SELECT COUNT(*) 
      FROM Modules 
     WHERE ModuleDefId = MD.ModuleDefId 
        AND IsDeleted = 1
    ) AS 'RecycleBinInstances' 
FROM DesktopModules DM
    INNER JOIN ModuleDefinitions MD 
        ON DM.DesktopModuleID = MD.DesktopModuleID 
GROUP BY DM.ModuleName, DM.Friendlyname, MD.ModuleDefId 
ORDER BY 'TotalInstances'

This query will list modules with following columns:

• ModuleName
• FriendlyName
• TotalInstances
• RecycleBinInstances