Check DNN version for all DotNetNuke instances on Server

Posted: May 11, 2010 | Author: | Filed under: SQL Server | 1 Comment

 

Run the following query, you can get versions of all your DNN installations and their created dates.

—Coyp the following code and run from SSMS. Or you can create a stored procedure to include the code and call your stored procedure.

create table #VersionCheck (Website varchar(128), Major int, Minor int, Build int)

EXEC

sp_MSForEachDB

‘ IF EXISTS (select 1 from [?].information_schema.tables where

table_name=”Version”)

BEGIN

INSERT INTO #VersionCheck

select top 1

(SELECT top 1 HTTPAlias+ ” | ”+ CONVERT(VARCHAR(10),Createddate,101) FROM [?].dbo.PortalAlias ORDER BY PortalAliasID ) as website, Major, Minor, Build

FROM [?].dbo.Version

order by VersionId DESC

END’

SELECT

Website, LEFT(‘0’+CAST(Major as varchar(2)),2) +‘.’+ LEFT(‘0’+CAST(Minor as varchar(2)),2) +‘.’+LEFT(‘0’+CAST(Build as varchar(2)),2) as DNN_Version FROM #VersionCheck

DROP

table #VersionCheck

Alter all tables in a database

Posted: May 11, 2010 | Author: | Filed under: SQL Server | Leave a comment
Here is a code sample how to add or drop a ccolumn from all tables within a database.
 
–EXEC sp_MSForEachTable ‘ IF exists (select 1 from Information_Schema.Columns  WHERE Table_Name = PARSENAME(”?”,1) AND Column_Name = ”Col1”)
–ALTER TABLE ?  DROP COLUMN Col1′
 
EXEC sp_MSForEachTable ‘ IF not exists (select 1 from Information_Schema.Columns  WHERE Table_Name = PARSENAME(”?”,1) and Column_Name = ”Col1”)
ALTER TABLE ?  ADD Col1 nvarchar(50) null’