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]
}
# 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]
}
# 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]
}
# 類似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輕松搞定。
浙公網安備 33010602011771號