--This script can be used to evaluate the complexity of a site
-- v 1.2 - Benjiro Nelson 2019 - DNN Support

DECLARE @Counter INT
DECLARE @CounterTable TABLE (PrintLine NVARCHAR(100))

INSERT INTO @CounterTable (PrintLine) VALUES ('------- General Table Sizes -------')
SET @Counter = (SELECT COUNT(*) FROM Portals); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the Portals table       -- Usually less than 100 rows')
SET @Counter = (SELECT COUNT(*) FROM PortalAlias); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the PortalAlias table       -- Usually between 1-2 times the number of portals')
SET @Counter = (SELECT COUNT(*) FROM PortalLanguages); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the PortalLanguages table       -- Usually between 1-3 times the number of portals')
SET @Counter = (SELECT COUNT(*) FROM PortalGroups); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the PortalGroups table       -- Usually zero, take notice if it is not')
SET @Counter = (SELECT COUNT(*) FROM Users); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the Users table       -- Usually less than 5,000 rows')
SET @Counter = (SELECT COUNT(*) FROM Roles); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the Roles table       -- Usually 10-20 times the number of portals')
SET @Counter = (SELECT COUNT(*) FROM Tabs); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the Tabs table       -- Usually less than 2,000 rows')
SET @Counter = (SELECT COUNT(*) FROM DesktopModules); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the DesktopModules table       -- Usually less than 200 rows')
SET @Counter = (SELECT COUNT(*) FROM WebServers); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the WebServers table       -- Usually less than 4 rows')
INSERT INTO @CounterTable (PrintLine) VALUES (' ')
INSERT INTO @CounterTable (PrintLine) VALUES ('------- Performance Related Table Sizes -------')
SET @Counter = (SELECT COUNT(*) FROM EventLog); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the EventLog table       -- Usually less than 10,000')
SET @Counter = (SELECT COUNT(*) FROM SiteLog); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the SiteLog table       -- Usually zero')
SET @Counter = (SELECT COUNT(*) FROM ScheduleHistory); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the ScheduleHistory table       -- Usually less than 10,000 rows')
SET @Counter = (SELECT COUNT(*) FROM ContentWorkflows); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' rows in the ContentWorkflows table       -- At least 3 times the number of portals')
SET @Counter = (SELECT COUNT(*) FROM Users WHERE IsDeleted = 1); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' deleted users       -- As close to zero as possible')
SET @Counter = (SELECT COUNT(*) FROM Tabs WHERE IsDeleted = 1); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' deleted pages       -- As close to zero as possible')
SET @Counter = (SELECT COUNT(*) FROM TabModules WHERE IsDeleted = 1); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' deleted modules from pages       -- As close to zero as possible')
SET @Counter = (SELECT COUNT(*) FROM CoreMessaging_MessageRecipients WHERE EmailSent = 0); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' unsent email notifications       -- As close to zero as possible')
INSERT INTO @CounterTable (PrintLine) VALUES (' ')
INSERT INTO @CounterTable (PrintLine) VALUES ('------- 3rd Party Modules -------')
INSERT INTO @CounterTable (PrintLine) SELECT [Name] + ' Version ' + [Version] FROM Packages WHERE Email NOT LIKE 'support@dnnsoftware.com' AND Email NOT LIKE '%support@dotnetnuke.com%'
INSERT INTO @CounterTable (PrintLine) VALUES (' ')
SET @Counter = (SELECT COUNT(*) FROM Sysobjects WHERE Xtype='U'); INSERT INTO @CounterTable (PrintLine) VALUES (FORMAT(@Counter,'N0') + ' database tables overall       -- Default is ~230 in 9.3.0')
INSERT INTO @CounterTable (PrintLine) VALUES (' ')
INSERT INTO @CounterTable (PrintLine) VALUES ('------- Upgrade History -------')
INSERT INTO @CounterTable (PrintLine) SELECT CONVERT(NVARCHAR,Major) + '.' + CONVERT(NVARCHAR,Minor) + '.' + CONVERT(NVARCHAR,Build) + ' on ' + CONVERT(NVARCHAR,CreatedDate) FROM [Version]

SELECT * FROM @CounterTable