Using Powershell get Values from sql table

Question

we know that we can use sql query window to get values from Database like "select * from....". Is there any way to get a value through powershell way. I found the way to database table itself but not to the values..

Ex:

Set-location SQLserver:\sql\default\localhost\databases\database\tables
Get-childitem 

Above command gives me tables in the DB , but how can i get the values from it.

Answer 1

Most concise and straightforward option is Invoke-SqlCommand

$results = Invoke-Sqlcmd -ServerInstance "mySqlServer" -Database "myDatabase" -Query "SELECT * FROM MyTable"

Answer 2

You can use this

$connectionString = “Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;”

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$query = “SELECT * FROM Tab”

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()