How to query SQL Server using PowerShell

Question

I have this code that I got from a website and it's connected to my SQL Server using window authentication but I'm not sure how can I choose a database and query some table?.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "server instance"
$s.ConnectionContext.LoginSecure=$true
$s.Databases | select name, size, status

If I run this code, it show me a list of databases but I want to choose a database called "LitHold" and query some table from that database inside.

If you can install Modules, I would take on this module powershellgallery.com/packages/SqlServer/21.1.18256 — Santiago Squarzon
– Santiago Squarzon, Commented Jan 13, 2022 at 20:12
The module contains the Invoke-Sqlcmd command; I would suggest having a look at that. — Thom A
– Thom A ♦, Commented Jan 13, 2022 at 20:22
DBAtools is another very recommended module but haven't tried it personally. — Santiago Squarzon
– Santiago Squarzon, Commented Jan 13, 2022 at 20:23
I'd assume so, as you don't create the connection yourself; it's all encapsulated inside the module. Though you could easily test this out, @HarvB . — Thom A
– Thom A ♦, Commented Apr 4, 2024 at 15:22

Cpt.Whale · Accepted Answer · 2022-01-13 20:55:56Z

3

For SMO like you have in your question, you can run queries that return data using ExecuteWithResults() like so:

$s =  New-Object Microsoft.SqlServer.Management.Smo.Server "server instance"
$db = $s.Databases.Item("master")

$query = "SELECT * FROM [master].[sys].[databases] ORDER BY [name];"
$result = $db.ExecuteWithResults($query)

# Show output
$result.Tables[0]

answered Jan 13, 2022 at 20:55

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ziico Over a year ago

It work, thank you so much bro. really appreciate it.

ziico Over a year ago

Hi bro, can you please answer another question for me. stackoverflow.com/questions/70705126/…

Collectives™ on Stack Overflow

How to query SQL Server using PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related