--Application: Clear EventLog in all DotNetNuke Databases
--Created By: Jonathan Sheely
DECLARE @DEBUG BIT
SET @DEBUG=1 --Switch from EXEC to PRINT
 
DECLARE @dbname NVARCHAR(100)
DECLARE @EventLogTableName NVARCHAR(100)
DECLARE @ParmDefinition NVARCHAR(100)
DECLARE @sqlstr NVARCHAR(MAX)
DECLARE @sqlout NVARCHAR(100)
SET @ParmDefinition = N'@objOut nvarchar(100) output';
DECLARE databases CURSOR FOR
SELECT [name] FROM sys.databases
 
OPEN databases
FETCH databases INTO @dbname
 
WHILE (@@FETCH_STATUS<>-1)
BEGIN
    SET @sqlstr='USE [' + @dbname + ']; 
        SELECT @objOut=1 
        FROM sys.tables 
        WHERE [name] = ''aspnet_Applications'';'
    SET @sqlout=NULL
    EXEC sp_executeSQL @sqlstr,@ParmDefinition,@objOut=@sqlout OUTPUT
    IF @sqlout=1
    BEGIN
        SET @sqlstr='USE [' + @dbname + ']; 
            SELECT @objOut=loweredApplicationName 
            FROM aspnet_Applications;'
        SET @sqlout=NULL
        EXEC sp_executeSQL @sqlstr,@ParmDefinition,
            @objOut=@sqlout OUTPUT
         
        IF @sqlout='dotnetnuke'
        BEGIN
            SET @sqlstr='USE [' + @dbname + ']; 
            SELECT @objOut=[name] 
            FROM sys.tables 
            WHERE [name] like ''%EventLog'';'
            SET @sqlout=NULL
            EXEC sp_executeSQL @sqlstr,@ParmDefinition,
                @objOut=@sqlout OUTPUT
                 
            IF @sqlout IS NOT null
            BEGIN
                SET @EventLogTableName=@sqlout
                SET @sqlstr = 'USE [' + @dbname + ']; 
                    TRUNCATE TABLE ' + @EventLogTableName +';'
                IF @DEBUG=0
                BEGIN
                    EXEC sp_executeSQL @sqlstr
                END
                ELSE
                BEGIN
                    PRINT @sqlstr
                END
            END
        END
    END
    FETCH databases INTO @dbname
END
CLOSE databases
DEALLOCATE databases