#PSTip Identifying SQL system objects using SMO

Note: This tip requires PowerShell 2.0 or above.

A SQL Server instance, by default, deploys a few system databases such as Temp DB, MSDB, Master, and Model DB. Other databases that we create are called user databases. When performing automated actions, we should ensure that we don’t modify any settings of these system databases or any system objects for that matter.

So, how do we identify what are the system objects?

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$server.Databases | Select Name, IsSystemObject

We can use the similar approach for finding tables that are system objects.

$server.Databases['DBName'].Tables | Select Name, IsSystemObject

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts

%d bloggers like this: