如何从 PowerShell 运行 SQL Server 查询? [英] How do you run a SQL Server query from PowerShell?

问题描述

有没有办法在本地机器上使用 Powershell 在 SQL Server 上执行任意查询?

Is there a way to execute an arbitrary query on a SQL Server using Powershell on my local machine?

推荐答案

对于需要仅使用 .NET 和 PowerShell(未安装其他 SQL 工具)来完成此操作的其他人，这里是我使用的功能:

For others who need to do this with just stock .NET and PowerShell (no additional SQL tools installed) here is the function that I use:

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

}

我一直在使用这个，我不知道谁写了哪些部分.这是从其他人的示例中提炼出来的，但已简化为清晰且仅需要什么，而无需额外的依赖项或功能.

I have been using this so long I don't know who wrote which parts. This was distilled from others' examples, but simplified to be clear and just what is needed without extra dependencies or features.

我经常使用和分享它，以至于我将它变成了 GitHub 上的脚本模块，以便您现在可以转到您的模块目录并执行 git clone https://github.com/ChrisMagnuson/InvokeSQL 并且从那时起 invoke-sql 将在您使用它时自动加载(假设您使用的是 PowerShell v3 或更高版本).

I use and share this often enough that I have turned this into a script module on GitHub so that you can now go to your modules directory and execute git clone https://github.com/ChrisMagnuson/InvokeSQL and from that point forward invoke-sql will automatically be loaded when you go to use it (assuming your using PowerShell v3 or later).

这篇关于如何从 PowerShell 运行 SQL Server 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！