如何在PowerShell中运行SQL Server查询？

Question

我能否在本地计算机上使用Powershell执行任意查询语句来操作SQL Server？

- Tigger78

Answer 1

对于其他需要只使用.NET和PowerShell（没有安装额外SQL工具）完成此操作的人，这是我使用的函数：

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$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

}

我使用这个已经很长时间了，我不知道哪些部分是谁写的。这是从别人的例子中提取出来的，但简化了以使清晰，并且只包含所需的内容，没有额外的依赖或功能。

我经常使用和分享这个东西，以至于我把它转变成了一个脚本模块放在GitHub上。现在你可以到你的模块目录下执行git clone https://github.com/ChrisMagnuson/InvokeSQL，从那之后，在使用时自动加载invoke-sql（假设你使用的是PowerShell v3或更高版本）。

- Chris Magnuson

---

@Maslow 我不能确定，我知道如果不处理对象，这个程序可以很好地运行，但是如果你有一个单独的 powershell.exe 进程，会在数周内多次调用它而不关闭，那么可能最终会成为问题，但你必须测试一下。 - Chris Magnuson

请注意，这会强制您编写可能容易受到 SQL 注入攻击的脚本，如果它们依赖于从依赖于用户输入的源读取查询数据。 - Joel Coehoorn

@JoelCoehoorn，您能向我们中的新手解释得更详细些吗？ - AllTradesJack

@AllTradesJack 谷歌 SQL 注入。Invoke-Sql 命令没有一种方法可以将参数与命令文本分开包含。这基本上保证你使用字符串拼接来构建查询，而这是一个很大的错误。请注意避免这种情况。 - Joel Coehoorn

对我而言运作良好。对于任何想知道如何处理对象的人，只需添加$connection.dispose()等即可。虽然我不知道这是否有任何区别。 - Nick.McDermaid

Answer 2

您可以使用 Invoke-Sqlcmd 命令

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"

http://technet.microsoft.com/en-us/library/cc281720.aspx

- manojlds

---

如果你在使用SQL Server的情况下，这可能很棒，但如果你在使用个人工作站，效果就不那么好了。 - aikeru

只要安装了 SQL Server 客户端工具（SSMS），您就可以在任何地方运行此程序。它可以在任何工作站上正常运行，无论该工作站是否正在运行 SQL Server。 - alroc

使用以下导入命令使 cmdlet 可用：Import-Module "sqlps" -DisableNameChecking。 - xx1xx

如果您仍在使用 SQL 2008 R2，则需要使用一个解决模块：http://sev17.com/2010/07/10/making-a-sqlps-module/ - Vincent De Smet

Invoke-SqlCmd是一个奇怪的边缘情况和不一致行为的无尽噩梦。它有时输出列，有时不输出列？我的错误消息在哪里？它在一个计算机上是这样，在另一个计算机上却不是？我该如何安装它？每个问题的答案比前一个更糟糕。 - Pxtl

Answer 3

此函数将查询结果作为 PowerShell 对象数组返回，以便您可以轻松地在过滤器中使用它们并访问列：

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
}

- mcobrien

---

为什么这比填充“DataTable”更可取（请参见Adam的答案）？ - alroc

可能并没有太大的区别，但一般来说更喜欢使用SqlDataReaders，因为它们消耗的资源较少。虽然在这种情况下这可能并不相关，但使用SqlDataReader返回真实的对象比返回DataTable更好，可以在foreach和where子句中使用而不必担心数据源。 - mcobrien

最好附上示例用法。 - user117499

有时候星星不够。 - Fred B

Answer 4

我在这篇博客中找到了一个示例。

$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("dbcc freeproccache", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -ne -1)
{
    echo "Failed";
}
$cn2.Close();

假设你可以在这里替换其他的TSQL语句：dbcc freeproccache。

- dmc

---

这个解决方案对我有效，但是ExecuteNonQuery()在成功时返回零，我使用的条件是：if ($cmd.ExecuteNonQuery() -ne 0)。 - Gixabel

似乎它返回受影响的行数。https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?view=netframework-4.7.2 - NicolasW

这种方法经常会失败，而且没有有用的错误消息，所以我采用了这种方法，尽管它需要安装额外的模块。https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps#example-11-connect-to-azure-sql-database-(or-managed-instance)-using-an-access-token - Nick.McDermaid

Answer 5

如果想要在本地计算机上完成而不是在 SQL 服务器的上下文中完成，则可以使用以下方法。这是我们公司使用的方法。

$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

只需填写$ServerName、$DatabaseName和$Query变量，就可以开始了。

我不确定我们最初是如何发现这个问题的，但有类似的东西在这里。

- Adam

Answer 6

Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3

- Vishe

---

它将显示PowerShell命令使用的SQL连接数。 - Vishe

Answer 7

目前并没有内置的“PowerShell”运行SQL查询的方法。如果您安装了SQL Server工具，那么您可以使用Invoke-SqlCmd命令。

由于PowerShell是基于.NET构建的，因此您可以使用ADO.NET API来运行您的查询。

- Aaron Jensen

Answer 8

为了避免在使用varchar参数时出现SQL注入问题，您可以使用

function sqlExecuteRead($connectionString, $sqlCommand, $pars) {

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $connection.Open()
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)

    if ($pars -and $pars.Keys) {
        foreach($key in $pars.keys) {
            # avoid injection in varchar parameters
            $par = $command.Parameters.Add("@$key", [system.data.SqlDbType]::VarChar, 512);
            $par.Value = $pars[$key];
        }
    }

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    return $dataset.tables[0].rows

}

$connectionString = "connectionstringHere"
$sql = "select top 10 Message, TimeStamp, Level from dbo.log " +
    "where Message = @MSG and Level like @LEVEL"
$pars = @{
    MSG = 'this is a test from powershell'
    LEVEL = 'aaa%'
};
sqlExecuteRead $connectionString $sql $pars

- Piero

---

我无法想象出一个 PowerShell 脚本容易受到 SQL 注入攻击的情况，但是相比手动构建查询语句，我更喜欢这种方式。我认为这样更易读。谢谢！ - 2b77bee6-5445-4c77-b1eb-4df3e5

Answer 9

你可以使用最好的SQL Server模块：DBATOOLS。同时，你也可以从多个SQL实例中运行查询并受益于此。

Install-Module dbatools -Scope CurrentUser

$sql = 'SQL1','SQL1\INSTANCE1','SQL2'
$query = "SELECT 'This query would run on all SQL instances'"

Invoke-DbaQuery -SqlInstance $sqlinstances -Query $query -AppendServerInstance

- PollusB

Answer 10

你甚至可以按照你想要的方式格式化字符串并传递参数。

case "ADDSQLSERVERUSER":
    //0 = coprorateName;
    //1 = user password
    //2 = servername
    command = @"$sqlQuery = Use JazzUWS_'{0}' 
                Create login UWSUser_'{0}' with password='{1}';
                Create user UWSUser_'{0}' for login UWSUser_'{0}';
                Grant Execute to UWSUser_'{0}';

                Use ReportSvrUWS_'{0}' 
                Create user UWSUser_'{0}' for login UWSUser_'{0}';
                Grant Execute to UWSUser_'{0}';

                Invoke-Sqlcmd -Query $sqlQuery -ServerInstance '{2}'";
    break;

远程执行的C#代码（您可以按照自己的方式进行组织）

        string script = PowershellDictionary.GetPowershellCommand("ADDSQLSERVERUSER");
        script = String.Format(script, this.CorporateName, password, this.SQLServerName)
        PowerShellExecution.RunScriptRemote(_credentials.Server, _credentials.Username, _credentials.Password, new List<string> { script });

- Majedur