Google Reviews
If you find this content or our services useful, please leave us a review.


This script will delete unused directories in the Directory table which are unused


CScript.exe {Script} {MSI}


'set up log file
Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

'create a name/path for log file
Dim MSIPath : Set MSIPath = fso.GetFile(WScript.Arguments(0))  
Dim logFile : logFile = Left(MSIPath.Path, InStrRev(MSIPath.Path, ".") - 1) & ".log"

Dim objLogFile : Set objLogFile = fso.OpenTextFile(logFile, ForAppending, True)

WriteLog "Removing Unused Directories"
WriteLog "Processing: " & MSIPath.Name

'create 2 constants - one for when we want to just query the MSI (read) and one for when we want to make changes (write)
Const msiOpenDatabaseModeReadOnly = 0
Const msiOpenDatabaseModeTransact = 1
Const msiViewModifyReplace = 4

'create WindowsInstaller.Installer object
Dim oInstaller : Set oInstaller = CreateObject("WindowsInstaller.Installer")

'open the MSI (the first argument supplied to the vbscript)
Dim oDatabase : Set oDatabase = oInstaller.OpenDatabase(WScript.Arguments(0),msiOpenDatabaseModeTransact) 
Dim sql, dirView, dirRecord, directoryView, directoryRecord, execView, tableView, tableRec, directoryParentRec, directoryParentView, tempTable, tempColumn, dirsToDeleteView, dirsToDeleteRec

If oDatabase.TablePersistent("Directory") = 1 Then

	' alter table to add a temporary column for storing a marker
	Set execView = oDatabase.OpenView("ALTER TABLE `Directory` ADD `InUse` SHORT TEMPORARY HOLD")
	'	set all the markers to 0
	Set execView = oDatabase.OpenView("UPDATE `Directory` SET `InUse`=0")

	'-- Try all the Directory_ Foreign key tables
	Set tableView = oDatabase.OpenView("SELECT `Table` FROM `_Columns` WHERE `Name`= 'Directory_' ") 'this lists the tables that have 'Directory_' columns
	Set tableRec = tableView.Fetch
	Do While Not tableRec is Nothing
		Set directoryView = oDatabase.OpenView("SELECT DISTINCT `Directory_` FROM `" & tableRec.StringData(1) & "`")
		Set directoryRecord = directoryView.Fetch
		While not directoryRecord is nothing
			Set directoryRecord = directoryView.Fetch
		Set tableRec = tableView.Fetch

	Set tableView = Nothing
	Set tableRec = Nothing

	Set tableView = oDatabase.OpenView("SELECT `Table`,`Column` FROM `_Validation` WHERE `Column`= 'DestFolder' OR `Column`= 'DirProperty' OR `Column`= 'SourceFolder' OR `Column`= 'ReserveFolder'") 
	Set tableRec = tableView.Fetch
	Do While Not tableRec Is Nothing
		tempTable = tableRec.StringData(1)
		tempColumn = tableRec.StringData(2)

		If oDatabase.TablePersistent(tempTable) = 1 Then
			Set directoryView = oDatabase.OpenView("SELECT DISTINCT `"& tempColumn &"` FROM `" & tempTable & "`")
			Set directoryRecord = directoryView.Fetch
			While Not directoryRecord is Nothing
				Set directoryRecord = directoryView.Fetch
		End If

		Set tableRec = tableView.Fetch


	Set tableView = Nothing
	Set tableRec = Nothing

	Set dirsToDeleteView = oDatabase.OpenView("SELECT `Directory`, `DefaultDir` FROM `Directory` WHERE `InUse`=0 ")
	Set dirsToDeleteRec = dirsToDeleteView.Fetch
	While not dirsToDeleteRec is nothing
		If Left(dirsToDeleteRec.StringData(2), 2) <> ".:" And Not isMSMData(dirsToDeleteRec.StringData(1)) And Not childIsMSMData(dirsToDeleteRec.StringData(1)) Then
			WriteLog "DELETE FROM `Directory` WHERE `Directory`= '" & dirsToDeleteRec.StringData(1) & "'"
			oDatabase.OpenView("DELETE FROM `Directory` WHERE `Directory`= '" & dirsToDeleteRec.StringData(1) & "'").Execute

		End If
		Set dirsToDeleteRec = dirsToDeleteView.Fetch

	Set execView = oDatabase.OpenView("ALTER TABLE `Directory` FREE ") '--remove the temporary column

End If

Set fso = Nothing
Set objLogFile = Nothing
Set execView = Nothing
Set tableRec = Nothing
Set tableView = Nothing
Set directoryView = Nothing
Set directoryRecord = Nothing
Set dirsToDeleteView = Nothing
Set dirsToDeleteRec = Nothing


Set oDatabase = Nothing
Set oInstaller = Nothing

Sub WriteLog(LogMessage)

	WScript.echo Now() & ": " & LogMessage
    objLogFile.Writeline(Now() & ": " & LogMessage)

End Sub

Sub MarkDir(sDirName)  

	Set execView = oDatabase.OpenView("UPDATE `Directory` SET `InUse`=1 WHERE `Directory` = '" & sDirName & "'")
	execView.Execute	' set the marker to 1 to indicate that the directory table entry is in use

	Set directoryParentView = oDatabase.OpenView("SELECT `Directory_Parent` FROM `Directory` WHERE `Directory` = '" & sDirName & "'") '--Done this one, now mark the parent directories
	Set directoryParentRec = directoryParentView.Fetch
	If not directoryParentRec is nothing Then

		MarkDir(directoryParentRec.StringData(1)) '-- recurse up the tree
	End If

	Set execView = Nothing
	Set directoryParentView = Nothing
	Set directoryParentRec = Nothing
End Sub

'this function is used to check child entries in the directory table, to make sure they're not from a merge module before
'the directory gets deleted

Function childIsMSMData(sDirectory)

	childIsMSMData = False

	sql = "SELECT `Directory` FROM `Directory` WHERE `Directory_Parent` = '" & sDirectory & "'" 
	Set dirView= oDatabase.OpenView(sql) 
	Set dirRecord = dirView.Fetch
	Do Until dirRecord Is Nothing

		If isMSMData(dirRecord.StringData(1)) Then
			childIsMSMData = True
			Exit Do
		End If	

		Set dirRecord = dirView.Fetch	

	Set dirView = Nothing
	Set dirRecord = Nothing

End Function

'returns true if sData contains MSM decoration
Function isMSMData(sData)
	isMSMData = False
	Dim Match
	Dim regEx : Set regEx = New RegExp
	regEx.MultiLine = vbTrue = vbTrue
	regEx.Pattern = "[A-Za-z0-9]{8}_[A-Za-z0-9]{4}_[A-Za-z0-9]{4}_[A-Za-z0-9]{4}_[A-Za-z0-9]{12}"
	For Each Match in regEx.Execute(sData)
		isMSMData = True
	Set regEx = Nothing
End Function


Delete Unused Directories
Comments have now been disabled. If you have a question to ask about this post please ask the community!