Cursors in Powershell

Published 24 January 11 10:39 AM | MartinBell

Invoke-cmd is a cmdlet available in the SQL Server Powershell snap-ins. I talked about these snapins in my blog post Using SQL Server in Powershell outside of SQLPS

Whilst I have seem many examples of invoking invoke-sqlcmd to do DML I can’t remember seeing anything on how to use the results of a query inside a Powershell script i.e. the equivalent of using a cursor in T-SQL.

So here is an example:

# load snap-in if not in SQLPS
Add-PSSnapin SqlServerCmdletSnapin100

foreach ( $datarow in ( invoke-sqlcmd -server localhost -database Adventureworks -query "select t.object_id AS tableid, t.name as tablename, c.object_id as columnId, c.name as columnname FROM sys.tables t JOIN sys.columns c ON c.object_id = t.object_id WHERE t.name = 'Employee'" ) ) {
    write-host $datarow.tablename $datarow.columnname ;
}

Each row (object) returned by the invoke-sqlcmd object is assigned to $datarow and you can then use the members of this object within the script.

Filed under:

Comments

# Dew Drop – January 25, 2011 | Alvin Ashcraft's Morning Dew said on January 25, 2011 01:08 PM:

Pingback from  Dew Drop – January 25, 2011 | Alvin Ashcraft's Morning Dew

# Martin Bell UK SQL Server MVP said on March 15, 2011 08:00 PM:

Changing a database collation is not necessarily the easy task it may initially seem to be. Because it can be quite complex Powershell seems a good choice for the tool to write your conversion script.

This Blog

SQL Blogs

Syndication