SQLPS Powershell in SQL Server -3

Now we will go for some cmdlets and see how to use them.
One question comes in your mind is how to run query from powershell?

Lets see first it
For this there is cmdlet -Invoke-Sqlcmd

1. Running SQL query by specifying Database name,
Here we are connected to server so no need to specify it.

So command is
1.Invoke-Sqlcmd -Database test -Query "Select * from sys.databases"

It will give result for this query .

2.Now we will also set database , so we dont need to give db name here.
In previous article I show how to use database.
Lets see  again

CD Databases 
CD Test

It will connect to database test.
Run this query

Invoke-Sqlcmd  -Query "Select * from sys.databases"

3. So you are aware how to use Invoke-Sqlcmd
If we want to get syntax on how to use it.

Get-Help Invoke-Sqlcmd -full 
It will not only give syntax but also give example how to use it.

4.Is Invoke-Sqlcmd is long for typing ?
We can set alias of it.

New-Alias tsql Invoke-Sqlcmd

Not tsql alias is set
So run query with this alias

tsql -Query "Select * from sys.tables"

5.Is result not good looking as we want?
Its due to command prompt console which has limitation to display result

so use Out-GridView

So the command is

tsql -Query "select * from sys.tables" | Out-GridView

No comments:

Post a Comment

Popular Posts