Monday, August 30, 2010

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!!

0 comments:

Post a Comment