34. PowerShell -- SQL Server的使用(2)

o2geao

　　参考：
　　http://www.shangxueba.com/jingyan/105946.html
　　一、先不用SqlServerCmdletSnapin100这个SnapIn来写几个操作常用数据的脚本
　　1. 由于有读者问如何用PowerShell显示数据库中表，以下是一个简单函数供参考
　　#==============================================
　　# SQL Server 2008 - PowerShell
　　# 显示用户表
　　#zivsoft
　　#==============================================
　　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
　　# 显示查询数据内容
　　#zivsoft
　　#==============================================
　　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>　　}
　　else {
　　#采用整合安全机制登陆
　　$login = "Integrated Security = True"
　　}
　　#数据库连接字符串
　　$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; $login"
　　}
　　4. 另一种风格的获取数据库数据
　　#================================================
　　# 类似DataTable GetDataTable(String strSQL)
　　#周利华
　　#================================================
　　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>　　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轻松搞定。