I like our videos, and I think they're a great way to teach, and an easy way to demo what we're trying to do. It's a lot easier and quicker than explaining things in text. BUT, they're not so great for reference after the first viewing. Sometimes I just want to FIND the CODE I NEED…
In that vein, I occasionally make the effort to partly transcribe an especially awesome video. Today, we have Sean's "Scripting DB Objects in PowerShell". Remember, this is a rough, incomplete transcription. More of a reference, which I hope will be really useful.
It's hard to get people jazzed about new technologies; PS is just another scripting language, so who cares? But this one is a lot easier to use, and it's got a lot to offer. (This was filmed while PS was in version 1.0). Today we'll talk about SQL PS and scripting databases and DB objects.
To open SQL Powershell, go to SQL Server Management Studio, right click on any object in the Object Explorer, and select Start PowerShell. You'll get a command line window that's context sensitive [2:50] – it starts in the tree position where you clicked: i.e., if you right-clicked on your server, the path may look something like this:
PS SQLSERVER:\SQL\SERVERNAME\DEFAULT>
If you right-clicked on a table, it might look like this:
PS SQLSERVER:\SQL\MySERVERNAME\DEFAULT\Databases\MyDBNAME>\Tables\dbo.MyTABLENAME>
We talked about navigating Powershell in the Powershell basics course. I like being able to traverse these things like i'm traversing a directory structure.
-
"Dir" will get you a list of all "folders" (objects) in that directory that you can access,
-
"cd" allows you to switch directories (object levels),
-
"gci" is an alias for "get child item" which gets a listing of objects in the directory (like dir does).
Example: If we're in the path ….Databases\MyDATABASE\Tables>, we can type gci | format-table, Name, Status to get a list of table names and statuses formatted as a table. [4:50] Use the -autosize flag to make the output more readable.
[6:10] To connnect to another box, you just have to change directory (cd). So from PS SQLSERVER:\sql\, do a cd OtherServerName\Default to connect to the default instance, or cd OtherServerName, where you can get a list of instances on that server (using dir).
[10:35] – Scripting objects. This is the cool stuff. There are all types of reasons to script out database objects. For example, we can make this part of our change control process…doing this by hand is kind of a pain. Being able to do it this way is great, because you can just call it from the command line.
Script DB objects: gci | % {$_.script()} Explanation: We're in the Tables node, so all child items (all tables) get piped (|) to a percent (%), which is an alias for FOR EACH. Variables start with $ in powershell, so $_ is the built in variable that holds the current cursor value. Reading the script line left to right, it says: get each child item (gci), pipe (|) it to a for each (%), which takes the current name ($_), and call the script method
(.script()).
[14:25] Basic filtering: gci *xyz* | % {$_.script()} will only get those with xyz in the name. You can also pipe them to the filter object and filter based on very rich criteria.
…
[~15:00] Discussion of all the different methods you can call – not just .Script(). You can find out all the available methods with gci | gm (get-childItem, piped to get-member).
[18:15] Pipe your object scripts to an out file: gci | % {$_.script()} | out-file c:\scripts\MyScript.txt
Happy days,
Jen McCown