Clean up Kepion database with SQL before sharing – Kepion Support Center

Introduction

You can use the following clean-up script in SQL Server Management Studio to prepare a Kepion application database to share or use as a template.
This script removes the following:
AD user information
Activity and logging information
Runtime apps information
Historic metadata information
Script

Attention: This script will PERMANENTLY alter and delete data from an application. DO NOT USE THIS ON A PRODUCTION SERVER OR DATABASE. Always verify that a functional backup exists before altering any data in these tables.
SET NOCOUNT ON;

--------------------------------------------------------------------------------
-- CLEAN UP MODELER
--------------------------------------------------------------------------------
DELETE FROM [dbo].[HistoricForms];
DELETE FROM [dbo].[HistoricRules];
DELETE FROM [dbo].[ProcessDetails];
DELETE FROM [dbo].[ApplicationDeployment];

IF OBJECT_ID(N'[dbo].[ApplicationDefinedSets]', N'U') IS NOT NULL
BEGIN
    UPDATE [dbo].[ApplicationDefinedSets]
    SET [ModifiedBy] = -2;
END;

UPDATE [dbo].[ApplicationDrillthroughDefinitions] SET [ModifiedBy] = -2;
UPDATE [dbo].[ApplicationMemberVariables] SET [ModifiedBy] = -2;
UPDATE [dbo].[ApplicationVariables] SET [ModifiedBy] = -2;
UPDATE [dbo].[DashboardForms] SET [ModifiedBy] = -2;
UPDATE [dbo].[DashboardPages] SET [ModifiedBy] = -2;
UPDATE [dbo].[Dashboards] SET [ModifiedBy] = -2;
UPDATE [dbo].[Dimensions] SET [ModifiedBy] = -2;
UPDATE [dbo].[FileStores] SET [ModifiedBy] = -2;

UPDATE [dbo].[Forms]
SET [ModifiedBy] = -2, [CreatedBy] = -2;

UPDATE [dbo].[Hierarchies] SET [ModifiedBy] = -2;

UPDATE [dbo].[HistoricForms]
SET [ModifiedBy] = -2, [CreatedBy] = -2;

UPDATE [dbo].[HistoricRules] SET [ModifiedBy] = -2;
UPDATE [dbo].[MappingFilters] SET [ModifiedBy] = -2;
UPDATE [dbo].[ModelDimensions] SET [ModifiedBy] = -2;
UPDATE [dbo].[Models] SET [ModifiedBy] = -2;
UPDATE [dbo].[Rules] SET [ModifiedBy] = -2;
UPDATE [dbo].[Scripts] SET [ModifiedBy] = -2;

IF OBJECT_ID(N'[dbo].[Partitions]', N'U') IS NOT NULL
BEGIN
    UPDATE [dbo].[Partitions]
    SET [ModifiedBy] = -2;
END;

--------------------------------------------------------------------------------
-- CLEAN UP ADMIN
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#AdUsers', 'U') IS NOT NULL
    DROP TABLE #AdUsers;

SELECT U.[UserID]
INTO #AdUsers
FROM [dbo].[Users] U
WHERE U.[Type] IN (1, 2, 5, 6);

SELECT *
FROM Constants

DELETE AU
FROM [dbo].[AuthorizationsUsers] AU
WHERE AU.[UserID] IN (SELECT [UserID] FROM #AdUsers);

DELETE APR
FROM [dbo].[AuthorizationsPageRestrictions] APR
WHERE APR.[UserID] IN (SELECT [UserID] FROM #AdUsers);

DELETE UA
FROM [dbo].[UserAffiliations] UA
WHERE UA.[UserID] IN (SELECT [UserID] FROM #AdUsers);

DELETE FROM [dbo].[ExtendedModelAccesses];
DELETE FROM [dbo].[ExtendedDimensionPermissions];

DELETE DDP
FROM [dbo].[DimensionDataPermissions] DDP
WHERE DDP.[UserID] IN (SELECT [UserID] FROM #AdUsers);

DELETE MA
FROM [dbo].[ModelAccesses] MA
WHERE MA.[UserID] IN (SELECT [UserID] FROM #AdUsers);

DELETE AA
FROM [dbo].[ApproverAssignments] AA
WHERE AA.[UserID] IN (SELECT [UserID] FROM #AdUsers);

DELETE FROM [dbo].[AppointedApprovers];

UPDATE [dbo].[Authorizations]
SET [CreatedBy] = -1, [ModifiedBy] = -1;

UPDATE [dbo].[AuthorizationsDashboardRules] SET [ModifiedBy] = -1;
UPDATE [dbo].[AuthorizationsNotificationTemplates] SET [ModifiedBy] = -1;
UPDATE [dbo].[AuthorizationsPageRestrictions] SET [ModifiedBy] = -1;
UPDATE [dbo].[AuthorizationsUsers] SET [ModifiedBy] = -1;
UPDATE [dbo].[Submissions] SET [CreatedBy] = -1;
UPDATE [dbo].[DimensionDataPermissions] SET [ModifiedBy] = -1;
UPDATE [dbo].[ModelAccesses] SET [ModifiedBy] = -1;
UPDATE [dbo].[UserAffiliations] SET [ModifiedBy] = -1;
UPDATE [dbo].[Users] SET [ModifiedBy] = -1;
UPDATE [dbo].[Htmls] SET [ModifiedBy] = -1;

--------------------------------------------------------------------------------
-- CLEAN UP LEGACY AUTHORIZATIONS
--------------------------------------------------------------------------------
DELETE DF
FROM [dbo].[DashboardForms] DF
INNER JOIN [dbo].[Authorizations] A
    ON DF.[DashboardID] = A.[DashboardID]
WHERE A.[Deleted] = 1;

DELETE ADR
FROM [dbo].[AuthorizationsDashboardRules] ADR
INNER JOIN [dbo].[Authorizations] A
    ON ADR.[AuthorizationID] = A.[AuthorizationID]
WHERE A.[Deleted] = 1;

DELETE AU
FROM [dbo].[AuthorizationsUsers] AU
INNER JOIN [dbo].[Authorizations] A
    ON AU.[AuthorizationID] = A.[AuthorizationID]
WHERE A.[Deleted] = 1;

DELETE FROM [dbo].[Authorizations]
WHERE [Deleted] = 1;

--------------------------------------------------------------------------------
-- CLEAN UP ACTIVITYID FROM FACTS
--------------------------------------------------------------------------------
DECLARE @Statement NVARCHAR(MAX);

DECLARE Statement_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT N'UPDATE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name) + N'
      SET [ActivityID] = NULL;'
FROM sys.tables t
WHERE t.name LIKE 'F[_]%[_]Writeback';

OPEN Statement_Cursor;
FETCH NEXT FROM Statement_Cursor INTO @Statement;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_executesql @Statement;
    FETCH NEXT FROM Statement_Cursor INTO @Statement;
END;

CLOSE Statement_Cursor;
DEALLOCATE Statement_Cursor;

DECLARE Statement_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT N'UPDATE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name) + N'
      SET [ActivityID] = NULL, [InitiatedActivityID] = NULL;'
FROM sys.tables t
WHERE t.name LIKE 'FH[_]%[_]Writeback';

OPEN Statement_Cursor;
FETCH NEXT FROM Statement_Cursor INTO @Statement;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_executesql @Statement;
    FETCH NEXT FROM Statement_Cursor INTO @Statement;
END;

CLOSE Statement_Cursor;
DEALLOCATE Statement_Cursor;

--------------------------------------------------------------------------------
-- CLEAN UP RUNTIME INFORMATION
--------------------------------------------------------------------------------
DELETE FROM [dbo].[Filter_Planning];
DELETE FROM [dbo].[Formula_CoordinateDefinition];
DELETE FROM [dbo].[Formula_Expression];
DELETE FROM [dbo].[Formula_RHSTupleReference];
DELETE FROM [dbo].[Formula_UniqueName];
DELETE FROM [dbo].[Activities];
DELETE FROM [dbo].[Submissions];
DELETE FROM [dbo].[Session];
DELETE FROM [dbo].[LoggingDashboardPageAccess];

--------------------------------------------------------------------------------
-- CLEAN UP LOGS
--------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[BlobStore]', N'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE [dbo].[BlobStore];
END;

IF OBJECT_ID(N'[dbo].[BlobStoreChunks]', N'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE [dbo].[BlobStoreChunks];
END;

IF OBJECT_ID(N'[dbo].[RuleComponentProgress]', N'U') IS NOT NULL
BEGIN
    DELETE FROM [dbo].[RuleComponentProgress];
END;

IF OBJECT_ID(N'[dbo].[RuleProgress]', N'U') IS NOT NULL
BEGIN
    DELETE FROM [dbo].[RuleProgress];
END;

IF OBJECT_ID(N'[deploy].[MultiDimensionalCube]', N'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE [deploy].[MultiDimensionalCube];
END;

IF OBJECT_ID(N'[deploy].[MultiDimensionalDimension]', N'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE [deploy].[MultiDimensionalDimension];
END;

IF OBJECT_ID(N'[log].[PerformanceLogs]', N'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE [log].[PerformanceLogs];
END;

IF OBJECT_ID(N'[log].[PerformanceLogDetails]', N'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE [log].[PerformanceLogDetails];
END;

--------------------------------------------------------------------------------
-- FINALLY, CLEAN UP USERS
--------------------------------------------------------------------------------
DELETE U
FROM [dbo].[Users] U
WHERE U.[Type] IN (1, 2, 5, 6);
Introduction

Script

Related articles
