Thursday, 4 March 2010

Getting to SQL Server from PowerShell script interface

Right, I'm progressing with PowerShell a little at a time. I still dont have any books but whenever I need to reference SQL information (not data, I am only getting info on Server settings not accessing customer database data) I try the PS route first.

In order to connect to a SQL Server from the command line you need to add two PS snapins. To do that you issue the commands add-PSSnapin sqlservercmdletsnapin100 and add-PSSnapin sqlserverprovidersnapin100

Today I am looking at collation on my server so I first need to 'map a drive' using new-PSDrive -name ~Drivename~ -psprovider SQLServer -root sqlserver:\SQL\~servername~\~instancename~ and then change my current drive to that server so I use set-location ~Drivename~:\databases and then simply use get-ChildItem |Select-Object name, collation to get a list of database names on that SQL Instance and their collation setting.

As always, if you want to see the extra switches or other information about PS commands then simply type get-help ~pscommand~ for the command you are interested in. eg. get-help new-PSDrive will return the help for the new-PSDrive command

Please be sure to read the code disclaimer page before you use any code in this blog.

No comments:


Bookmark and Share
Google Analytics Alternative