如何从 PowerShell 运行 SQL Server 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8423541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you run a SQL Server query from PowerShell?
提问by Chris Magnuson
Is there a way to execute an arbitrary query on a SQL Server using Powershell on my local machine?
有没有办法在本地机器上使用 Powershell 在 SQL Server 上执行任意查询？
回答by Chris Magnuson
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:
对于需要仅使用股票 .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
}
I have been using this so long I don't know who wrote which parts but this was distilled from other's examples but simplified to be clear and just what is needed without extra dependencies or features.
我一直在使用它，我不知道谁写了哪些部分，但这是从其他示例中提炼出来的，但简化为清晰，只是需要的内容，没有额外的依赖项或功能。
I use and share this often enough that I have turned this into a script module on GitHubso that you can now go to your modules directory and execute git clone https://github.com/ChrisMagnuson/InvokeSQLand from that point forward invoke-sql will automatically be loaded when you go to use it (assuming your using powershell v3 or later).
我经常使用和分享这个，我已经把它变成了GitHub 上的一个脚本模块，这样你现在可以转到你的模块目录并执行git clone https://github.com/ChrisMagnuson/InvokeSQL，从那时起 invoke-sql 将在你使用它时自动加载（假设您使用的是 powershell v3 或更高版本）。
回答by manojlds
You can use the Invoke-Sqlcmdcmdlet
您可以使用Invoke-Sqlcmdcmdlet
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
回答by dmc
Here's an example I found on this blog.
这是我在这个博客上找到的一个例子。
$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();
Presumably you could substitute a different TSQL statement where it says dbcc freeproccache.
大概你可以在它说的地方替换一个不同的 TSQL 语句dbcc freeproccache。
回答by mcobrien
This function will return the results of a query as an array of powershell objects so you can use them in filters and access columns easily:
此函数将查询结果作为 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
}
回答by arnav
Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3
回答by Adam
If you want to do it on your local machineinstead of in the context of SQL server then I would use the following. It is what we use at my company.
如果您想在本地计算机上而不是在 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
Just fill in the $ServerName, $DatabaseNameand the $Queryvariables and you should be good to go.
只需填写$ServerName、$DatabaseName和$Query变量就可以了。
I am not sure how we originally found this out, but there is something very similar here.
我不确定我们最初是如何发现这一点的，但这里有一些非常相似的东西。
回答by Aaron Jensen
There isn't a built-in "PowerShell" way of running a SQL query. If you have the SQL Server tools installed, you'll get an Invoke-SqlCmdcmdlet.
没有运行 SQL 查询的内置“PowerShell”方式。如果您安装了SQL Server 工具，您将获得一个Invoke-SqlCmdcmdlet。
Because PowerShell is built on .NET, you can use the ADO.NET APIto run your queries.
由于 PowerShell 是基于 .NET 构建的，因此您可以使用ADO.NET API来运行您的查询。
回答by Piero
To avoid SQL Injection with varchar parameters you could use
为避免使用 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 = "..."
$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
