PowerShell查询sql server

最新推荐文章于 2025-09-06 22:56:13 发布

转载最新推荐文章于 2025-09-06 22:56:13 发布 · 169 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dreamer-fish/p/3941169.html

文章标签：

#数据库

本文介绍如何使用PowerShell脚本执行SQL Server查询，并提供了几个实用的SQL Server查询函数，包括函数Invoke-SQL、SQL和SQLQuery3。通过这些函数，可以有效地从数据库中获取数据。

function Invoke-SQL1：

function Invoke-SQL {
    param(
        [string] $DBServer,
        [string] $Database,
        [string] $SQLCMD
      )

    $connectionString = "Data Source=$DBServer; " + "Integrated Security=SSPI; " + "Initial Catalog=$Database"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($SQLCMD,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables

}
$DBServer = "scvmm"
$Database = "appcontroller"
$SQLCMD = "select * from [authz].[UserRoleMembers]"


Invoke-SQL $Server $Database $SQLCMD

function SQL2：

function sql($sqlText, $database = "master", $server = ".")
{
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

    $connection.Open();
    $reader = $cmd.ExecuteReader()

    $results = @()
    while ($reader.Read())
    {
        $row = @{}
        for ($i = 0; $i -lt $reader.FieldCount; $i++)
        {
            $row[$reader.GetName($i)] = $reader.GetValue($i)
        }
        $results += new-object psobject -property $row            
    }
    $connection.Close();

    $results
}

SQL Query3：

$ServerName = "_ServerName_"
$DatabaseName = "_DatabaseName_"
$Query = "SELECT * FROM Table WHERE Column = ''"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables