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轻松搞定。