SQL Server

Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 4)

7/20/2011 11:39:05 AM

Getting Table Properties

Another common task is to get a row count of the tables in a particular database:

PS SQLSERVER:\SQL\<servername>\<instancename>\Databases\
AdventureWorks2008R2\Tables> Get-ChildItem .| Sort-Object -descending|Select-
Object -First 10| Format-Table -autosize Name,RowCountNote


An easy-to-remember alias for Get-ChildItem is basically dir.

In the preceding example using the AdventureWorks2008R2 database, the top 10 tables with the highest row count value are returned.


The preceding example shows how many features are packaged within PowerShell, which applies not only to SQL tables, but also to all .NET objects. Simply using Get-Item on a particular table returns only the default properties of Schema, Name, and Created. Piping something like Get-Item [table_name] to either Format-Table * or Get-Member exposes all the properties available for a particular object.

Cmdlet Example: Invoke-SqlCmd

The Invoke-SqlCmd cmdlet was mentioned earlier. It will likely be the most commonly used cmdlet currently provided. Here is a simple example using this cmdlet:

Invoke-sqlcmd -query "exec sp_help"

Using Invoke-SqlCmd, you can simply pass any T-SQL–based query as a value to the cmdlet. The preceding basic example is provided by running a basic built-in stored procedure: sp_help.

This example demonstrates several important issues, especially how powerful the provider can be. Based on the location in the SQL provider, some of the values passed to the cmdlet are automatically provided to the cmdlet by the provider itself: the server and database to query aren’t required in the command line.

Let’s consider this example a bit further and do a few extra things with it.

First, this cmdlet can accept input from the pipeline:

"exec sp_help"|ForEach-Object{Invoke-SqlCmd $_}

The preceding line demonstrates a few more issues that have already been discussed: the ForEach-Object cmdlet, the special variable $_, and also the way parameters can automatically match values to parameters even when the parameter name isn’t explicitly added to the command entered.

The sp_help stored procedure provides a lot of information. What if only the extended stored procedures were required?

When Get-Member is used, the members from this particular query are inspected, and it is determined that the Object_type property is the value that indicates what kind of stored procedure is being dealt with.

The query to get only extended stored procedures is now the following:

"exec sp_help"|ForEach-Object{Invoke-SqlCmd $_}| `
Where{$_.Object_type -eq "extended stored proc"}|Select Name

Finally, the output consists only of extended stored procedures.

Cmdlet Example: Invoke-PolicyEvaluation

Another one of the provided cmdlets is Invoke-PolicyEvaluation. This cmdlet is used to specify a SQL Server Policy-Based Management policy (or policies) that will be evaluated against the target server. You can easily cycle through all the available policies and evaluate each one, or simply provide a list of policies to evaluate separated by a comma. Consider this example:

sl "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation -Policy "Database Auto Shrink.xml" -TargetServerName

The preceding command returns the output to the console of the result of the policy evaluation. By default, the particular policy passed to the cmdlet is only checked. In other words, by default, properties are actually changed so that they are now compliant with the policy.

Joining Columns

Quite often, databases provide a table for users. Frequently, these users have their last name and first name in separate columns. Because these are typically simple strings, a feature, already discussed, allows two strings to be easily joined together.

The following code snippet shows how two columns from the AdventureWorks2008R2 database are easily joined together from within the SQL minishell:

PS SQLSERVER:\SQL\D830\DEFAULT\databases\adventureworks2008r2> Invoke-SqlCmd
"Select * from Person.Person"| `
>> Select-Object -First 10|ForEach-Object{$_.LastName + ", " + $_.FirstName}
S´nchez, Ken
Duffy, Terri
Tamburello, Roberto
Walters, Rob
Erickson, Gail
Goldberg, Jossef
Miller, Dylan
Margheim, Diane
Matthew, Gigi
Raheem, Michael

Here, the first 10 records are selected, and then the LastName and FirstName values are combined together.

Retrieving an Entry

On occasion, you might need to get a particular entry from a table. When the following code snippet is run, an array is automatically returned:

PS SQLSERVER:\SQL\D830\DEFAULT\databases\adventureworks2008r2>  Invoke-SqlCmd
"Select * from Person.Person"

PowerShell provides a simple way to look at particular elements within an array. In the following example, entry 100 is returned and then entries 95 to 105:
PS SQLSERVER:\SQL\D830\DEFAULT\databases\adventureworks2008r2>  (Invoke-SqlCmd
"Select * from Person.Person")[100]
PS SQLSERVER:\SQL\D830\DEFAULT\databases\adventureworks2008r2> (Invoke-SqlCmd
"Select * from Person.Person")[95..105]


The first element of an array is the element zero; therefore, the first record in an array is retrieved by referencing the element id [0].

Other -----------------
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 1) - General Tasks & Scheduling Scripts
- PowerShell in SQL Server 2008
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Using Database Object Schemas
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Protection via Normalization
- Troubleshooting and Optimizing SQL Server 2005 : Server Configuration Maintenance
- Troubleshooting and Optimizing SQL Server 2005 : Tuning the Database Structure
- Troubleshooting and Optimizing SQL Server 2005 : Data Analysis and Problem Diagnosis
- SQL Injection Attacks and Defense : Exploiting the Operating System - Consolidating Access
- SQL Injection Attacks and Defense : Executing Operating System Commands
- Administering SQL Server 2008 with PowerShell : PowerShell Scripting Basics (part 2)
- Administering SQL Server 2008 with PowerShell : PowerShell Scripting Basics (part 1)
- Administering SQL Server 2008 with PowerShell : Overview of PowerShell
- SQL Server 2008 Scheduling and Notification : Scripting Jobs and Alerts, Multiserver Job Management & Event Forwarding
- SQL Server 2008 Scheduling and Notification : Managing Alerts
- SQL Injection Attacks and Defense : Accessing the File System (part 2) - Writing Files
- SQL Injection Attacks and Defense : Accessing the File System (part 1) - Reading Files
- SQL Server 2008 Scheduling and Notification : Managing Jobs
- SQL Server 2008 Scheduling and Notification : Managing Operators
- SQL Server 2008 Scheduling and Notification : Configuring the SQL Server Agent
- SQL Server 2008 : Database Mail - Related Views and Procedures
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8