PowerShell provides an extremely efficient method to globally clean up and drop objects within your SQL Server estate based on some pattern matching in the object names. Some of the reasons you may wish to do this are: -
- You want to remove some third party objects that have a consistent naming convention
- You have deprecated objects by renaming them in the first instance and now wish to clean up by dropping them entirely
- You have moved certain objects to an alternative instance and wish to drop the original objects
The following script will remove any views with "DropMe" in the name from all databases in all instances defined within a file called C:\PowerShell\Instances.csv
Import-Csv 'C:\PowerShell\Instances.csv' | % {
$Instance = $_.Instance
Get-SqlDatabase -ServerInstance $_.Instance} | % {
foreach ($View in $_.Views){
if ($View.IsSystemObject -eq 0 -and $View.Name -like '*DropMe*') {
$View.Drop()
write-host "$View has been dropped from the $_ database on the $Instance instance"
}}}
There is only one problem with this approach - it doesn't work. The following glaring error is generated, never a good sign when you see the dreaded red text
Collection was modified; enumeration operation may not execute
You cannot iterate over a collection, the views collection in this instance, and modify that collection by destroying objects.
I solved this issue by writing all the objects to be dropped to another array. An iteration is then performed over this new array, dropping each view: -
$ViewsToDrop = @()
Import-Csv 'C:\PowerShell\Instances.csv' | % {
$Instance = $_.Instance
Get-SqlDatabase -ServerInstance $_.Instance} | % {
$Views = $_.Views
foreach ($View in $Views) {
if ($View.IsSystemObject -eq 0 -and $View.Name -like '*DropMe*') {
$ViewsToDrop += $View.Name
}
}
foreach ($ViewToDrop in $ViewsToDrop) {
if($Views.Contains($ViewToDrop)) {
$Views[$ViewToDrop].Drop()
Write-Host "$ViewToDrop has been dropped from the $_ database on the $Instance instance"
}
}
}
You may wish to rename objects you suspect are no longer required rather than simply dropping them. This is to facilitate an easy rollback and in our case we are going to append "DropMe" to the name. This allows for a period of monitoring and due diligence to ensure the object is indeed not required. The above script will then iterate over your entire SQL Server estate to remove views with "DropMe" in the name.
You could easily extend this script to include other objects types. In addition, you could only drop objects modified over three months ago for example or add any other filters appropriate to your business.