Yesterday we looked at how you can navigate and retrieve items in the SQL Server provider using common Powershell cmdlets like Get-ChildItem and Set-Location. These are part of a set of core cmdlets that are implemented across providers to give a consistent experience to users. The remaining two cmdlets that we did not cover handle other tasks than simply finding and getting objects and, as such, require a little more care.
Rename-Item does exactly what the name says, rename something. With this, we can rename database objects directly from the command line. Nothing fancy, but gives us an easy way to manage our objects. Let’s see how it’s used:
#Change to the context of my dummy database CD SQLSERVER:\SQL\localhost\DEFAULT\Databases\dummy #Rename a table within dummy dir tables Rename-Item tables\dbo.Junk Garbage dir tables
Because of the intricacies of implementing a cmdlet like Rename-Item, it is specific about the information you pass to it. You’ll note that our Rename-Item call references the table object specifically by schema name, but the new table name is simply the new name within the schema.
This is due to how Rename-Item is working behind the scenes. Remember how we talked about using the SMO? The SMO performs all of its actions via T-SQL, meaning that everything we do via the provider also can be boiled down to a T-SQL command. If we start an extended events session to track SQL statements, our rename action becomes exposed:
As you can see, the rename is being executed by sp_rename. DBAs have been using this to rename objects for decades and SQLPS is no different. While we’re using a different interface to manage our SQL Servers, the underlying action is the same.
What does this mean for the Remove-Item cmdlet? We already know that it is meant to delete files in our file system provider, so that means we’re probably going to be dropping objects in the SQL Server provider. Let’s take a look by using Remove-Item to drop our table:
#Drop the Garbage table dir tables Remove-Item tables\dbo.Garbage dir tables
Just like that, our table is gone. Note, we still had to specify the schema in order to remove the correct table. If you had left the schema out, the provider would have thrown an error to warn you that it didn’t have enough information to carry out the action. We can also go back to our extended events session to validate what was done:
Now, while we can rename and remove database objects, what about creating new objects? As of this time, New-Item is currently not officially implemented. This makes sense because of all the variance within SQL for how different objects can be created and what would need to be declared for that. Creating a new login, table, or index have vastly different statements for those objects, meaning it would be simpler to create database objects using T-SQL than through a provider method.
New-Item can be used in certain spots in the provider, but those methods are currently undocumented and hard to find. I still don’t have a good grasp on where this cmdlet has been implemented. We will cover this a little more as well, but for the time being assume that New-Item can not be used.
Wrapping up the first week of this series, you should have a good idea of what the provider is and how to use it. If you’re getting started with Powershell to manage SQL Server, this is where to start. Even with just accessing and querying objects through the provider, a lot of scripting and automation options open op. Next week we’ll cover some basic scripting and automation patterns that can be used within the provider, helping you build out your use of Powershell within your SQL Server environments.
I’m tweeting!