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]
}