· Uwe Janke
Commands / Remove-sqmMasterDbCustomObjects

Remove-sqmMasterDbCustomObjects

Database HealthsqmSQLTool v1.9.110+ · Remove🔓 sysadmin, or ALTER ANY SCHEMA + CONTROL on the objects
Fixes what Get-sqmMasterDbCustomObjects finds. Per instance it first re-detects the custom objects live (sys.objects, is_ms_shipped = 0, minus the configured MasterDbObjectWhitelist and anything matched by -ExcludeObject) rather than trusting a possibly stale object handed in through the pipeline. If -ObjectName is given, only those names (wildcards allowed) are considered; otherwise every non-whitelisted object found is a candidate. An instance with nothing left to remove is skipped entirely. Each remaining object is dropped with the statement matching its type (DROP TABLE / DROP VIEW / DROP PROCEDURE / DROP FUNCTION), each wrapped in its own try/catch so one failing object does not stop the rest. A CSV changelog is written per instance into -OutputPath and copied to the module's central path via Copy-sqmToCentralPath, the same way Repair-sqmDbOwnerRisk and Set-sqmDatabaseOwner do it. The whitelist that protects objects is configured module-wide with Set-sqmConfig -MasterDbObjectWhitelist; there is deliberately no per-call switch to bypass it.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestring[]Optional$env:COMPUTERNAMEOne or more instances. Pipeline-capable by property name.
-SqlCredentialPSCredentialOptional, SQL or Windows credential. Without it: Windows authentication.
-ObjectNamestring[]Optionalevery non-whitelisted objectObject name(s) to remove, wildcards allowed. Binds ObjectName from Get-sqmMasterDbCustomObjects through the pipeline.
-ExcludeObjectstring[]Optional@()Additional object names to leave alone even though they are not whitelisted (wildcards allowed).
-OutputPathstringOptionalGet-sqmDefaultOutputPath\MasterDbCustomObjectsRemovalDirectory for the CSV changelog.
-ContinueOnErrorswitchSwitch$falseContinue with the next instance on error.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately instead of returning as errors.
-WhatIf / -ConfirmswitchSwitchConfirmImpact = HighDrops are high-impact, so PowerShell prompts by default. -WhatIf lists what would be dropped without touching anything.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES Re-detect custom objects live per instance sys.objects, is_ms_shipped = 0, minus whitelist and -ExcludeObject A pipeline object is never trusted as-is - it may be stale -ObjectName given? NO Every non-whitelisted object is a removal candidate YES Restrict to the named objects (wildcards allowed) Objects outside the list stay untouched Anything left to remove? NO Skip the instance No changelog file is written YES DROP TABLE / VIEW / PROCEDURE / FUNCTION Statement matched to the object type · one try/catch per object A single failing object never stops the rest Write CSV changelog + Copy-sqmToCentralPath Same audit trail as Repair-sqmDbOwnerRisk / Set-sqmDatabaseOwner Return [PSCustomObject] per object SqlInstance · ObjectName · ObjectType · Action · Status · Message DONE

Examples

Dry run: show which objects would be dropped
Remove-sqmMasterDbCustomObjects -SqlInstance 'SQL01' -WhatIf
Clean up exactly what the report function found
Get-sqmMasterDbCustomObjects -SqlInstance 'SQL01' | Where-Object Status -eq 'Custom' | Remove-sqmMasterDbCustomObjects
Remove a single family of leftovers without prompting
Remove-sqmMasterDbCustomObjects -SqlInstance 'SQL01' -ObjectName 'usp_Test*' -Confirm:$false
Sweep several instances but keep one object
Remove-sqmMasterDbCustomObjects -SqlInstance 'SQL01','SQL02' -ExcludeObject 'usp_MyAdminTool' -ContinueOnError -WhatIf
← Back
Command Index
Related
Get-sqmMasterDbCustomObjects
Related
Set-sqmConfig