How do you query a database using PowerShell?

You can invoke the SQLCMD from PowerShell with the invoke-sqlcmd commandlet.

MSDN defines Invoke-SQL CMD thus,

"Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility."

Now as to the exact procedure, you need to get to Powershell with the SQL Server snap-ins.

If you run the SQLPS.exe which is installed with your SQL Server installation(SQL Server 2012 for example) then you have got yourself a good start.

 

Double clicking SQLPS.exe in your computer will bring up the PowerShell command line for SQL Server as shown for both 2012 and 2016 CTP2 versions of SQL Server.

 

 

 

sqlps2012.png

sqlps2016ctp2.png

I will be using the 2012 version for this post.
Now you need to run the invoke-sqlcmd commandlet as shown. This is specific for my installation of SQL Server and you need to change parameters around. I have a SQL Server named instance RegencyPark on my laptop called Hodentek8 and I have chosen Windows authentication for access.
Now I run the following statement:
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

I immediately get a response to my query as shown:
-----------------------------------
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

BusinessEntityID      : 1
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Ken
MiddleName            : J
LastName              : Sánchez
Suffix                :
EmailPromotion        : 0
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : 92c4279f-1207-48a3-8448-4636514eb7e2
ModifiedDate          : 2/8/2003 12:00:00 AM
BusinessEntityID      : 2
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Terri
MiddleName            : Lee
LastName              : Duffy
Suffix                :
EmailPromotion        : 1
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : d8763459-8aa8-47cc-aff7-c9079af79033
ModifiedDate          : 2/24/2002 12:00:00 AM
That is all there is to it.