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

標籤：powershell

參考：

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 Id = $UserName; Password = $Password"
　　　 }
　　　 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 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

650) this.width=650;" title="點擊查看大圖" alt="916x203" src="http://img.shangxueba.cn/jyimg/20130206/214343JT-0.jpg" width="630" height="139" />

最後，補充，如果直接用SQL Server 2008的Management Studio進去開啟PowerShell，便可以直接操 作類似Invoke-Sqlcmd的cmdlets，但是如果沒有Management Studio怎麼辦呢？

很簡單，用Add-PSSnapin SqlServerCmdletSnapin100輕鬆搞定。

本文出自 “Ricky's Blog” 部落格，請務必保留此出處http://57388.blog.51cto.com/47388/1643937

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