I might be a SQL Server pro, but I’m still a PowerShell novice. I learn best by doing, so I keep pushing myself to do more with PowerShell so that I can learn more.
If you don’t already know this about the SqlServer PowerShell Module, you can access SQL Server via a path, as if it were a drive.
SQLSERVER:\
The general format would be to use a path something like this: SQLSERVER:\SQL\ComputerName\InstanceName
If you do a dir or Get-ChildItem on that path, you’ll see that the contents looks a heck of a lot like SSMS’s Object Explorer for the same instance (though, not identical):
Both SQL Server Management Studio and PowerShell leverage SMO to communicate with the server, so they’ll get the same data from the server, and just handle the display differently.
The general navigation holds true all the way down to the object level. If I do a Get-ChildItem drilling down to the Views “folder”, using the path SQLSERVER:\SQL\ComputerName\InstanceName\Databases\DatabaseName\Views I can see the full list of all views in that database:
Other than the funky line wrap, do you notice any problems with it? Anything missing?
System Views
I recently noticed that the system objects were missing from my results when I do a Get-ChildItem. I noticed it with views, but then realized that none of the system objects showed up. What gives? I floundered through a quick Google search, where I knew I wasn’t searching for the right thing, and was not surprised when I didn’t see the answer.
I said to myself, “Andy, hold on a second & think. If something doesn’t want to open up, sometimes you just have to -force it open.”
So I tried Get-ChildItem <path> -Force to see what happened:
Boom. That did it. It seems obvious in hindsight, but it wasn’t so obvious a few days ago.