PowerShell数据库巡检脚本

此脚本采用Powershell4.0 版本编写，都过调用NPOI类库来实现自动获取sql语句执行结果并把每条语句的结果保存到excel文件的每个sheet中，sql语句保存在csv文件中，格式如下：

sqlname,sqltext

sheetname,sql

脚本会读取csv文件中的每条语句，并把执行结果保存在excel中，废话不多说，直接上代码：

 

#配置信息
$Server = "192.168.1.10" #数据库服务器IP或实例名
$Database = "master" #数据库名称
$UserName = "sa" #数据库用户
$Password = "Carlson2013" #用户密码

$folder = "c:\PowerShell-SQL\Excel" #保存巡检结果的文件夹
$sqls = Import-Csv -Path "c:\PowerShell-SQL\sqls.csv" #sql语句

# 建立数据库连接.
function New-SqlConnection()
{

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
try{
$SqlConnection.Open()
#Write-Host 'Connected to sql server.'
return $SqlConnection
}
catch [exception] {
Write-Error ('Connect to database failed with error message:{0}' -f ,$_)
$SqlConnection.Dispose()
return $null
}
}

#连接特定数据库
function New-SqlConnection2($Database)
{

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
try{
$SqlConnection.Open()
#Write-Host 'Connected to sql server.'
return $SqlConnection
}
catch [exception] {
Write-Error ('Connect to database failed with error message:{0}' -f ,$_)
$SqlConnection.Dispose()
return $null
}
}

# 查询返回一个DataTable对象

function Get-DataTable
{
param
(
[System.Data.SqlClient.SqlConnection]$SqlConnection,
[string]$query
)
$dataSet = new-object "System.Data.DataSet" "WrestlersDataset"
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query,$SqlConnection)
$dataAdapter.Fill($dataSet) | Out-Null
return $dataSet.Tables | select -First 1
}

 

function DataTable-EXCEL($sql,$sheet,$dbname)
{
$SqlConn = New-SqlConnection2($dbname)
$SqlCmd = $SqlConn.CreateCommand()
$dataSet = new-object "System.Data.DataSet" "WrestlersDataset"
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter"

$SqlCmd.commandtext = $sql.sqltext
$dataAdapter.SelectCommand = $SqlCmd
$dataAdapter.Fill($dataSet) | Out-Null
$dt = $dataSet.Tables[0]
$rowIndex = 1
$headerRow = $sheet.CreateRow(0)

for($c = 0;$c -lt $dt.Columns.Count;$c++)
{
$colname = $dt.Columns[$c].ColumnName
$headerRow.CreateCell($c).SetCellValue($colname)
}

for($r=0;$r -lt $dt.Rows.count;$r++)
{
$dataRow = $sheet.CreateRow($rowIndex)
for($c = 0;$c -lt $dt.Columns.Count;$c++)
{
$value = $dt.Rows[$r][$c]
$dataRow.CreateCell($c).SetCellValue($value)
}
$rowIndex++
}


$SqlConn.Dispose()

}

 

#创建文件夹
function New-Directory($path){
if((Test-Path $path) -ne $True)
{
$path = MD $path
}
return $path
}

#获取所有数据库名称
function get-dbList(){
$dblist = New-Object System.Collections.ArrayList
$dbssql = "select name from sys.databases where name not in ('master','tempdb','model','msdb','distribution')"
$sqlconn = New-SqlConnection
$dbs = Get-DataTable $sqlconn $dbssql

for($d=0;$d -lt $dbs.Rows.count;$d++ )
{
for($j=0;$j -lt $dbs.Columns.Count;$j++ )
{
#$dbs.Rows[$d][$j] | out-file D:\dbs.txt -append
$dbname = $dbs.Rows[$d][$j]
$dblist.add($dbname)
}

}
return $dblist
}

#创建EXCEL工作薄
function New-Workbook()
{
[void][reflection.assembly]::Loadfile("c:\PowerShell-SQL\NPOI.dll")
$workbook = new-object NPOI.HSSF.UserModel.HSSFWorkbook
return $workbook
}

#获取所有数据库的执行结果
function get-AllDB()
{
[void][reflection.assembly]::Loadfile("c:\PowerShell-SQL\NPOI.dll")

$folder = New-Directory $folder
$dbList = get-dbList
for($db=$dblist.Count/2;$db -lt $dblist.Count;$db++)
{
$workbook = new-object NPOI.HSSF.UserModel.HSSFWorkbook
$excelname = $dbname = $dblist[$db]
$filepath = new-object "System.IO.FileStream"("$folder\$excelname.xls",4,3)
foreach($sql in $sqls)
{
$sheetName = $sql.sqlname
$sheet = $workbook.CreateSheet($sheetName)
DataTable-EXCEL $sql $sheet $dbname

}

$workbook.write($filepath)
$filepath.close()
$workbook = $null
}

}

#获取指定数据库的执行结果
function get-OneDB($dbname)
{
[void][reflection.assembly]::Loadfile("c:\PowerShell-SQL\NPOI.dll")
$workbook = new-object NPOI.HSSF.UserModel.HSSFWorkbook
#$workbook = New-Workbook

$folder = New-Directory $folder
$excelname = $dbname
$filepath = new-object "System.IO.FileStream"("$folder\$excelname.xls",4,3)
foreach($sql in $sqls)
{
$sheetName = $sql.sqlname
$sheet = $workbook.CreateSheet($sheetName)
DataTable-EXCEL $sql $sheet $dbname

}

$workbook.write($filepath)
$filepath.close()
$workbook = $null

}

 

$starttime = get-date

#查询指定数据库
#get-OneDB $Database

#查询所有数据库
get-AllDB

$endtime = get-date

Write-Host -ForegroundColor yellow ('Total Runtime:{0} Minutes ' -f ($endtime - $starttime).TotalMinutes)

 

转载于:https://www.cnblogs.com/zuowei/p/PowerShell-SQL.html