Skip to content

Instantly share code, notes, and snippets.

@jsheely jsheely/file1.sql
Created Jan 10, 2013

Embed
What would you like to do?
Clear EventLog On All DotNetNuke Databases
--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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.