Exploring SQL Databases Using SQL Server Management Objects (SMO) and PowerShell

Continuing with my little SQL and PowerShell series, I am going to talk about how you can use PowerShell to explore SQL databases using SMO.

As shown in the previous articles, I will make my initial connection to a SQL server.

## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

## Connect to SQL Server
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('Boe-PC\SHIRE')

Viewing all of the databases and their properties is as simple as doing this:

$sqlServer.Databases

image

Of course, you get EVERYTHING with this. It may be better to filter out only the properties I need.

##Databases
$sqlServer.Databases | 
Format-Table Name,Owner,Status,LastBackupDate,
LastDifferentialBackupDate,LastLogBackupDate,
RecoveryModel,DataSpaceUsage -AutoSize

image

Much better. Here we can see some relevant information such as when backups have last taken place as well as the recovery model for each database among other things. Of course, you can choose what you feel like are the most important things to view.

Typically when working with objects and you want to only get a specific item from the collection, you would need to do a Where-Object and filter for a specific property (Name in this case). Luckily, you don’t have to do this at all. You can just run the following command to grab a lone database.

$db = $sqlServer.Databases['AdventureWorks']
$db.name

image

Now I have access to a single database to work with and view other interesting things on it.

Something that I think is really cool is that you can generate the TSQL code to create the database just by using the Script() method.

#Show Database TSQL Script
$db.Script()

image

Pretty cool stuff if you wanted to create another database somewhere else.

Finding all of the users that have logins added to a specific database can be found on the Users property. Within that object is a method called EnumRoles()  which will also show you what roles each user is in.

#Show Users with their roles
$db.Users | ForEach {
    [pscustomobject] @{
        Name = $_.Name
        Login = $_.Login
        LoginType = $_.LoginType
        UserType = $_.UserType
        HasDBAccess = $_.HasDBAcces
        CreateDate = $_.CreateDate
        Database = $_.Parent
        Roles = $_.EnumRoles()
    }
} | Format-Table -AutoSize

image

This provides a nice way to track who has access to what databases on your SQL Server.

In the flip side, you can also look at the available roles on the database.

#Roles
$db.Roles | ForEach {
    [pscustomobject] @{
        Name = $_.name
        Database = $_.Parent
        CreateDate = $_.CreateDate
        LastModified = $_.DateLastModified
        Owner = $_.Owner
        Users = $_.EnumMembers()
    }
} | Format-Table -AutoSize

image

If you want to check out the schemas on the database, you can run this.

#Look at schemas
$db.Schemas | FT Name,Parent,Owner -AutoSize

image

Let’s take a look at the permissions on the database.

#Database Permissions
$db.EnumDatabasePermissions() | 
Format-Table Grantee,GranteeType,PermissionType,
PermissionState,Grantor -AutoSize

image

We can also look at any locks that are on the database using the EnumLocks() method.

#Database Locks
$db.EnumLocks() | Format-Table -AutoSize

image

Depending on the database, you may have some database logon mappings.

$sqlServer.Databases.EnumLoginMappings()

image

You can check to see if any transactions are running and then view those transactions on the database.

If ($db.GetTransactionCount() -gt 0) {
    $db.EnumTransactions()
}

Depending on what is happening, you may or may not get anything. In my case, I ran the following command to ‘monitor’ transactions to show what is happening when I run a simple TSQL command to inserta row.

while ($True) {$db.EnumTransactions()}

image

You can view all of the objects on a database using the EnumObjects() method.

$db.EnumObjects()

image

Yes, there will be a lot of objects returned when you run this command.

If you want to know the size of your data files (both .mdf and .ldf) as well as what their growth rate might be, then the following lines of code will be pretty useful for you.

First off is the database file/s. First we can check out the FileGroups property to see how many file groups we have associated with this database.

$db.FileGroups

 

image

Only one file group to work with here. We can dig deeper by looking at the Files property to see more information about the database file.

$db.FileGroups.Files

 

image

Here you can see the actual size of the file (Size) and the size actually being used by the database (UsedSpace). Also, you are able to see more information like its growth rate. Lets do some formatting to make this a little easier to read.

$db.FileGroups.Files | Select FileName,Name,
@{L='FileSizeMB';E={[math]::Round(($_.Size * 1024)/1MB,2)}},
@{L='DatabaseSizeMB';E={[math]::Round(($_.UsedSpace * 1024)/1MB,2)}},
MaxSize,@{L='PhysicalDiskFreeSpaceMB';E={[math]::Round(($_.VolumeFreeSpace * 1024)/1MB,2)}},
Growth,GrowthType  | Format-Table -AutoSize

 

image

Here you can see where the file is located at, the size of the file (4MB) as well as how much of that size is actually being used by the database (2.88MB). The max size is –1, basically meaning that this thing is a goldfish and will grow to the size of the bowl (or drive in this case) if allowed. We can also see that this will grow by 10% each time its DatabaseSizeMB out-grows the FileSizeMB. So it may be a good idea to not only give it a maxsize but to also change the growth rate on this database to prevent a potential disaster.

Not to be left out, we can also check out the transaction log size in a similar way by looking at the LogFiless property of the database.

$db.LogFiles

image

Much like the database files, lets format it a little.

$db.LogFiles | Select FileName,Name,
@{L='FileSizeMB';E={[math]::Round(($_.Size * 1024)/1MB,2)}},
@{L='TLogSizeMB';E={[math]::Round(($_.UsedSpace * 1024)/1MB,2)}},
MaxSize,@{L='PhysicalDiskFreeSpaceMB';E={[math]::Round(($_.VolumeFreeSpace * 1024)/1MB,2)}},
Growth,GrowthType  | Format-Table -AutoSize

image

 

You can even run TSQL using the ExecuteWithResults() method. In this case I want to see all of the tables on this database.

$db.ExecuteWithResults('SELECT * FROM INFORMATION_SCHEMA.TABLES').Tables.Rows

image

I will be finishing up this article by showing you how you can create a database, make some configuration changes, verify it exists and then removing the database from the SQL server.

First, lets create a database on the SQL server..

#Create a database quickly
([Microsoft.SqlServer.Management.Smo.Database]@{
    Name ='ProxTest'
    Parent = $sqlServer
}).Create()

image

Now we can make some configuration changes to this database. But first we need to show the settings prior to making the change.

$newDb | Format-Table Name,Owner,Auto*Statistics* -AutoSize 

image

Now we can make some changes.

$newDB.SetOwner("boe-pc\Administrator")
$newDB.AutoCreateStatisticsEnabled = $True
$newDB.AutoUpdateStatisticsAsync = $True
$newDB.AutoUpdateStatisticsEnabled = $True
$newDB.Alter()

The Alter() is important because if I don’t call it, no changes will be made to the database. With that, lets check out those properties again.

$newDb | Format-Table Name,Owner,Auto*Statistics* -AutoSize

image

As you can see, the properties have been updated with a new owner as well as some statistics options.

Now, I really don’t need this database, so lets use the Drop() method to remove the database.

$newDB.Drop()
$sqlServer.Databases.name

image

No more database!

That is all for this article on exploring the database using SMO. The next article will talk about backing up and restoring a database using SMO.

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, SQL and tagged , , , . Bookmark the permalink.

5 Responses to Exploring SQL Databases Using SQL Server Management Objects (SMO) and PowerShell

  1. Chris Fitch says:

    Instead of *1024/1MB just use /1KB

  2. bjorn80 says:

    Great article! I am having the same problem as Scott, how can we get get the mdf view of the database?

  3. Sarvesh says:

    Excellent article..i am new to powershell and this is very informative.

  4. Hi! I appreciate this great article. I had trouble with this line: $db.FileGroups.Files. “Files” is not part of the options for me as shown by IntelliSense using ISE. When I run $db.FileGroups, “PRIMARY” is returned. $db.FileGroups.Parent.PrimaryFilePath gives me the directory path, but I’d like to get the file name for the database and log too. I’m using PowerShell 3.0. Do you know what might be the issue please?
    Here is the script:
    $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server(‘WORKPC’)
    $db = $sqlServer.Databases[‘master’]
    $db.name # this returns the name

  5. Pingback: Backup a SQL Database Using SQL Server Management Objects (SMO) and PowerShell | Learn Powershell | Achieve More

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s