Execute Scripts from a folder using Powershell

For last few days I am kinda got wired up with Powershell and trying to do whatever I used to do otherwise using SQL PowerShell e.g. other day we had some 40 odd scripts to be deployed on our database so I worte a simple 2 liner SQL PS script which would go and execute each script one by one.

1. To illustrate I have 3 Scripts in a folder called ScritpsFolder to create 3 Stored Procedures

2. Script inside sp_A

3. Now the Powershell code to execute scripts from the folder.

ForEach ($S In Gci -Path "C:\ScriptsFolder\" -Fliter *.sql | Sort-Object Name )
{
Invoke-SqlCmd -InputFile $S.FullName
}

a) ForEach : will iterate on each item insire $S.
b) Gci (Get-Child Item): will get all the filenames from given path and will store in variable $S.
c) Invoke-SqlCmd: will take the script and run it on the Database.


To run the script you should be in context of your SQL Server/Database and particular database if it not defined in the script.

4. Check the SPs created in the database

Script Out Database objects using PowerShell

PowerShell is a very powerful tool and sometimes provide an amazing way for accomplishing which otherwise would have taken lot of effort and time.. One such scenario I had recently when I had to script out few stored procedure from my SQL Sever Database. There we go.. Below I will explain how we can script out Database Objects using PowerShell.

Lets get rolling and start PowerShell-  We start PowerShell directly from SSMS by Right clicking any node and select Start PowerShell. It will open PowerShell connected to your Server

I will script Stored Procedures from the Database and I will show various options of doing that.

First let get down to Stored Procedures

1. First thing first .. let try to display our Stored Procedure on Screen

gci *uspGetAllNodes* | %{$_.Script()}

2. Displaying it on Screen doesnt help much, lets Sript-Out this in a file

gci *uspGetAllNodes* | %{$_.Script()} | Out-File c:\Scripts\uspGetAllNodes.sql

 

3. Thats cool we can get one Stored Procedure, how about getting all the StoredProcedures

gci  | %{$_.Script()} | Out-File c:\Scripts\AllStoredProcs.txt

4. Great, but what if I have few Stored procedures to extract and I dont what to run the script again and again.

Use an Array Variable.

$SPNameList = @("uspGetAllNodes", "uspGetAllPaths")
FOREACH ( $SP in $SPNameList)
{
gci *$SP* | %{$_.Script()} | out-file c:\Scripts\$SP.sql
}

5. OutPut Directory

Thanks!!