GetDacpacDependency.ps1
|
<#
.SYNOPSIS This command will read the dacpac file & generate a result for entire dacpac which will display that a component/element references which object .DESCRIPTION The Result can be generated on either Console or File. This can be decided by The options provided in ResultOuput Property Use "Verbose" Option to get more details about the Operation / Process performed, as Dacpac Reading Process takes time .PARAMETER DacpacPath Accepts the path where Dacpac is placed. .PARAMETER ResultOutput Accepts either Console or File, to determine where result is to be shown / placed. .EXAMPLE Get-DacpacDependency -DacpacPath 'C:\Users\Databases\AdventureWorksDev.dacpac' -ResultOutput Console -Verbose Get-DacpacDependency -DacpacPath 'C:\Users\Databases\AdventureWorksDev.dacpac' -ResultOutput File -Verbose #> FUNCTION Get-DacpacDependency() { Param ( [Parameter(Mandatory=$true)] [string] $DacpacPath , [Parameter(Mandatory=$true)] [ValidateSet('Console','File')] [string] $ResultOutput ) cls $Stopwatch = [system.diagnostics.stopwatch]::StartNew() Write-Verbose 'Starting Process to read Dacapac' FUNCTION GenerateXML ( [string] $DacpacPathMain) { $CopyPath = $DacpacPathMain.Substring(0, $DacpacPathMain.lastIndexOf('\')) $CopyPathM = $CopyPath + '\' + 'logs.zip' Copy-Item -Path $DacpacPathMain -Destination $CopyPathM -Force $UnZipnewPth = $CopyPathM.Substring(0, $CopyPathM.lastIndexOf('\')) $UnZipnewPth = $UnZipnewPth + '\' + '\logs' Expand-archive -path $CopyPathM -destinationpath $UnZipnewPth -Force $XMLAPath = $UnZipnewPth + '\model.xml' Copy-Item -Path $XMLAPath -Destination ( $CopyPath + '/' + 'DacpacXML.xml' ) -Force $XML = Get-Content -Path $XMLAPath | %{$i = 1} { new-object psobject -prop @{LineNum=$i;Text=$_}; $i++} Return $XML } $output = New-Object -Type PSObject $output | Add-Member -Name 'ObjectList' -Type NoteProperty -Value "" Function Functionexec() { $xmla = GenerateXML -DacpacPathMain $DacpacPath $xmla = [xml]$xmla.Text If ($xmla.HasChildNodes -eq $true) { $elementcount = $xmla.DataSchemaModel.Model.Element.Count Write-Output "ID^|Element^|References" for($i = 0 ; $i -le $elementcount ; $i++) { if($xmla.DataSchemaModel.Model.Element[$i].Type -ne 'SqlDatabaseOptions' -and $xmla.DataSchemaModel.Model.Element[$i].Type -ne 'SqlDefaultConstraint' -and $xmla.DataSchemaModel.Model.Element[$i].Type -ne 'SqlMasterKey' -and $xmla.DataSchemaModel.Model.Element[$i].Type -ne 'SqlSchema' ) #ignoring DatabseOptions part { if($xmla.DataSchemaModel.Model.Element[$i].HasChildNodes -eq $true ) { Write-Verbose "Reading Element :::: $($xmla.DataSchemaModel.Model.Element[$i].Name)" $relationshipcount = $xmla.DataSchemaModel.Model.Element[$i].Relationship.Count if($relationshipcount -ne $null -and $relationshipcount -ne 0 ) { for($j = 0 ; $j -le $relationshipcount ; $j++) { if($xmla.DataSchemaModel.Model.Element[$i].Relationship[$j].HasChildNodes -eq $true) { $entrycount = $xmla.DataSchemaModel.Model.Element[$i].Relationship[$j].Entry.Count if($entrycount -ne $null) { for($k = 0 ; $k -le $entrycount ; $k++) { foreach($l in $xmla.DataSchemaModel.Model.Element[$i].Relationship[$j].Entry[$k].References) { IF($l.ExternalSource -eq $null) { Write-Output "$($i)^|$($xmla.DataSchemaModel.Model.Element[$i].Name)^|$($l.Name)" } } foreach($l in $xmla.DataSchemaModel.Model.Element[$i].Relationship[$j].Entry[$k].Element) { IF($l.ExternalSource -eq $null) { Write-Output "$($i)^|$($xmla.DataSchemaModel.Model.Element[$i].Name)^|$($l.Name)" } } } } else { Write-Output "$($i)^|$($xmla.DataSchemaModel.Model.Element[$i].Name)^|$($xmla.DataSchemaModel.Model.Element[$i].Relationship[$j].Entry.References.Name)" } } } } else { Write-Output "$($i)^|$($xmla.DataSchemaModel.Model.Element[$i].Name)^|$($xmla.DataSchemaModel.Model.Element[$i].Relationship.Entry.References.Name)" } } } } } } $output.ObjectList = Functionexec $FinalOutput = $output.ObjectList | Get-Unique $ResultPath = $DacpacPath.Substring(0, $DacpacPath.lastIndexOf('\')) IF($ResultOutput -eq 'Console') { Write-Output 'Dependencies:' $FinalOutput Write-Verbose 'Result is displayed in Console' } Else { $Result1Path = $ResultPath + '\' + 'DependenciesResult.csv' $FinalOutput | Out-File -FilePath $Result1Path Write-Verbose "Dependecy Analysis Result is placed on : $($Result1Path)" } Write-Verbose 'Deleteing Excess FilesCreated' Remove-Item -Path ($ResultPath + '\' + 'logs.zip') -Force -Recurse Remove-Item -Path ($ResultPath + '\' + 'logs') -Force -Recurse Remove-Item -Path ($ResultPath + '\' + 'DacpacXML.XML') -Force -Recurse Write-Verbose 'Process Complete' Write-Verbose "Time Elapsed in Seconds : $($StopWatch.Elapsed)" } Export-ModuleMember -Function Get-DacpacDependency |