架构人生

PowerShell在SQL Server 2008中一些用法 [实例]

PowerShell早在SQL Server 2005里就已经被集成了, 而我第一次用却在SQL Server 2008中。今天有空总结几个实际例子出来。欢迎这方面专家来完善一下:

一、先不用SqlServerCmdletSnapin100这个SnapIn来写几个操作常用数据的脚本

1. 由于有读者问如何用PowerShell显示数据库中表,以下是一个简单函数供参考

#==============================================
#
 SQL Server 2008 - PowerShell
#
 显示用户表
#
 <c>zivsoft</c>
#
==============================================
function ShowCustomizedDataTable{
    
$SQLSERVER=read-host "Enter SQL Server Name:"
    
$Database=read-host "Enter Database Name:"
    
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    
$CnnString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
    
$SqlConnection.ConnectionString = $CnnString
    
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    
$SqlCmd.CommandText = "select name from sysobjects where type='u'"
    
$SqlCmd.Connection = $SqlConnection
    
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    
$SqlAdapter.SelectCommand = $SqlCmd
    
$DataSet = New-Object System.Data.DataSet
    
$SqlAdapter.Fill($DataSet)
    
$SqlConnection.Close()
    
$DataSet.Tables[0]
}

 

2. 显示SQL查询出来的数据

#==============================================
#
 SQL Server 2008 - PowerShell
#
 显示查询数据内容
#
 <c>zivsoft</c>
#
==============================================
function Get-DataTable([string]$query)
{
    
$dataSet= new-object "System.Data.DataSet" "DataSetName"
    
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($query$CnnString)
    [void] 
$da.Fill($dataSet)
    
return $dataSet.Tables[0]
}

 

3. 构建数据库联接字符串


###################################################################################################
#
 www.zivsoft.com
#
 设置数据库连接字符串
#
##################################################################################################
function global:Set-SqlConnection( $Server = $(Read-Host "SQL Server Name"), $Database = $(Read-Host "Default Database"),  $UserName , $Password  ) 
{
    
#如果用户名和密码都不为空
    if( ($UserName -gt $null-and ($Password -gt $null)) {
        
$login = "User Id = $UserName; Password = $Password"
    } 
    
else {
        
#采用整合安全机制登陆
        $login = "Integrated Security = True"
    }
    
#数据库连接字符串
    $SqlConnection.ConnectionString = "Server = $Server; Database = $Database; $login"
}

 

4. 另一种风格的获取数据库数据

#================================================
#
 类似DataTable GetDataTable(String strSQL)
#
 <author>周利华</author>
#
================================================
function global:Get-SqlDataTable( $Query = $(Read-Host "输入SQL语句")) 
{
    
#打开数据库
    if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
    
    
#实例化SQLCommand
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $Query$SqlConnection
    
    
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    
$SqlAdapter.SelectCommand = $SqlCmd
    
    
$DataSet = New-Object System.Data.DataSet
    
$SqlAdapter.Fill($DataSet| Out-Null
    
    
$SqlConnection.Close()
    
    
#返回数据库表
    return $DataSet.Tables[0]
}

 

二、以上是普通PowerShell通过ADO.NET操作数据库,下面列出更酷的SQL Server集成的PowerShell命令

先看一下Invoke-Sqlcmd这个关键的cmdlet的帮助信息:


NAME
    Invoke
-Sqlcmd
    
SYNOPSIS
    Runs a script containing statements from the languages (Transact
-SQL and XQuery) and commands supported by the SQL Server sqlcmd utility.
    
    
--------------  Example 1 --------------
    
    C:\PS
>Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
    
    
    This example connects to a named instance of the Database Engine on a computer and runs a basic Transact
-SQL script.
    
    
    TimeOfQuery
    
-----------
    
10/7/2007 1:04:20 PM
    
    
--------------  Example 2 --------------
    
    C:\PS
>Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSqlCmd.sql" | Out-File -filePath "C:\MyFolder\TestSqlCmd.rpt"
    
    
    This example reads a file containing Transact
-SQL statements and sqlcmd commands, runs the file, and writes the output to another file. Ensure all output files are secured with the appropriate NTFS permissions.
    
    
    Output sent to TestSqlCmd.rpt.
    
    
--------------  Example 3 --------------
    
    C:\PS
>$MyArray = "MYVAR1='String1'""MYVAR2='String2'"
    Invoke
-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2;" -Variable $MyArray
    
    
    This example uses an array of character strings as input to the 
-Variable parameter. The array defines multiple sqlcmd variables. The $ signs in the SELECT statement that identify the sqlcmd variables are escaped using the back-tick (`) character.
    
    
    Var1                        Var2
    
----                        ----
    String1                     String2
    
    
--------------  Example 4 --------------
    
    C:\PS
>Set-Location SQLSERVER:\SQL\MyComputer\MyInstance
    Invoke
-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance (Get-Item .)
    
    
    This example uses Set
-Location to navigate to the SQL Server PowerShell provider path for an instance of the Database Engine. Then the example uses Get-Item to retrieve an SMO Server object for use as the -ServerInstance parameter of Invoke-Sqlcmd.
    
    
    TimeOfQuery
    
-----------
    
10/18/2007 8:49:43 PM
    
    
--------------  Example 5 --------------
    
    C:\PS
>Invoke-Sqlcmd -Query "PRINT N'abc'" -Verbose
    
    
    This example uses the PowerShell 
-Verbose parameter to return the message output of the PRINT command.
    
    
    VERBOSE: abc
    
    
--------------  Example 6 --------------
    
    C:\PS
>Set-Location SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks
    Invoke
-Sqlcmd "SELECT DB_NAME() AS DatabaseName;"
    
    
    This examples uses a positional string to supply the input to the 
-Query parameter. It also shows how Invoke-Sqlcmd uses the current path to set the database context to AdventureWorks.
    
    
    WARNING: Using provider context. Server 
= MyComputer, Database = AdventureWorks.
    
    DatabaseName
    
------------
    AdventureWorks
    

 

仔细读完这个帮助,发现,上面所有对.NET Framework中ADO.NET的操作全可以用Invoke-Sqlcmd代替,非常简洁方便。

比如,获取home数据中所有用户表:

Invoke-Sqlcmd -Query "use home;SELECT name as tablename from sysobjects where xtype='U'" -QueryTimeout 3 | ft -auto   

 

比如,显示home数据库中userinfo表内容:

Invoke-Sqlcmd -Query "use home;SELECT * from UserInfo" -QueryTimeout 3 | ft -auto   

 

最后,补充,如果直接用SQL Server 2008的Management Studio进去打开PowerShell,便可以直接操作类似Invoke-Sqlcmd的cmdlets,但是如果没有Management Studio怎么办呢?

很简单,用Add-PSSnapin SqlServerCmdletSnapin100轻松搞定。

 

posted on 2009-05-07 15:33 周利华 阅读(...) 评论(...) 编辑 收藏

导航

公告