Listing DotNetNuke Database versions in all your databases

When you need to upgrade multiple DotNetNuke websites it is sometimes hard to figure out what version a specific site is. Of course, you could logon to all your sites, but that takes quite some time.

If you have installed the databases of your sites in one sql server instance, there is another option available. You could open all databases, open the Version table, and check the highest version number in that table. This is still a lot of work though… however, it is something that can be automated with a little effort.

The first part is getting the DNN version from a DNN database. This can be done by this select statement:

SELECT top 1 Major*100+Minor*10+Build AS [Version] FROM [Version] ORDER BY VersionId DESC

This select statement will give you the value 500 for DotNetNuke 5.0.0. Of course, we will run into an issue if the database was installed using an objectQualifier. Since DNN does not store the value of that objectQualifier in the database, we need to be a little more flexible. The following script will first get the internal object id of the version table in the database. It will pick the first available table that has a name that ends in “version” and has a column with the name “major”. Next, it will use that table to get the version in a similar way as above.

DECLARE @TableId intSELECT @TableId = idFROM dbo.syscolumnsWHERE [name]='major'AND id IN 
(SELECT idFROM dbo.sysobjectsWHERE [name] LIKE '%version'AND  OBJECTPROPERTY(id, N'IsUserTable') = 1)
DECLARE @VersionTable NVARCHAR(100)
SELECT @VersionTable = [name]  FROM dbo.sysobjects WHERE id = @TableIdDECLARE @SQLString nvarchar(500);DECLARE @ParmDefinition nvarchar(500);DECLARE @IntVersion int;DECLARE @IntVariable int;SET @SQLString = N'select  top 1 @Version = major*100+minor*10+build  from ' + @VersionTable + '  order by versionid DESC'SET @ParmDefinition = N'@Version INT OUTPUT';EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@Version = @IntVersion OUTPUT;PRINT @IntVersion

If you run this script from SQL Server Management Studio, it should give you the value 500 for a DotNetNuke 5.0 database.The next step is to create a stored procedure that will be stored in the Master database. This stored procedure will return a select that contains both the Database name and the DNN Version:

USE [master]
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_GetDNNVersionOfDB]
@DNNVersion INT OUTPUTASBEGINSET NOCOUNT ON;IF EXISTS (SELECT *FROM dbo.syscolumnsWHERE [name]='major'AND id IN 
(SELECT idFROM dbo.sysobjectsWHERE [name] LIKE '%version'AND  OBJECTPROPERTY(id, N'IsUserTable') = 1))
BEGINDECLARE @TableId intSELECT @TableId = idFROM dbo.syscolumnsWHERE [name]='major'AND id IN 
(SELECT idFROM dbo.sysobjectsWHERE [name] LIKE '%version'AND  OBJECTPROPERTY(id, N'IsUserTable') = 1)
DECLARE @VersionTable NVARCHAR(100)
SELECT @VersionTable = [name]  FROM dbo.sysobjects WHERE id = @TableIdDECLARE @SQLString nvarchar(500);DECLARE @ParmDefinition nvarchar(500);DECLARE @IntVersion int;DECLARE @IntVariable int;SET @SQLString = N'select  top 1 @Version = major*100+minor*10+build  from ' 
+ @VersionTable + '  order by versionid DESC'SET @ParmDefinition = N'@Version INT OUTPUT';EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@Version = @IntVersion OUTPUT;SELECT @DNNVersion = @IntVersionSELECT DB_NAME() AS datatabaseName,@IntVersion AS DNNVersion;ENDEND

The above SPROC will actually first test if it can find a DNN version table in the database, and will only return something if this table is found. If you execute this sproc like this:

USE [DotNetNuke_Cambrian] -- change this in the name of your database!DECLARE @RC intDECLARE @DNNVersion intEXECUTE @RC = [sp_GetDNNVersionOfDB] @DNNVersion OUTPUT

it should give you back something like this for a DNN 5.0 database:

The second stored procedure is much easier, as it will simply call the first stored proc for every database in the table. This uses an undocumented SQL Server Stored Procedure “sp_MSforeachdb”. This will allow you to execute commands for every database.

USE [master]
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_GetAllDNNVersions] ASBEGINSET NOCOUNT ONCREATE TABLE #foo 
( 
DatabaseName VARCHAR(255), 
DNNVersion INT 
) 
INSERT #fooEXEC sp_MSforeachdb @command1 ="use [?];declare @DNNVersion INT;EXEC sp_GetDNNVersionOfDB @DNNVersion OUTPUT;"SELECT DatabaseName, DNNVersion FROM #foo order by databasenameDROP TABLE #foo
END

The above sproc will create a temp table #foo, and will insert output of sp_GetDNNVersionOfDB for every database in #foo. Next, the content of table #foo is displayed, and the temp table is deleted. For my local development system it give these results:

Databases dnn4900q and DotNetNuke_Cambrian both have an object qualifier set, as you see this is handled just fine. Also, the server contains more non dnn databases, and these are all excluded, as expected.

For this solution i used information from these sources:

• http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
• http://www.sql-server-performance.com/articles/dba/ac_sql_server_2000_undocumented_sp_p1.aspx
• http://www.sqlservercentral.com/articles/Administering/sp_msforeachworker/711/