Quick Post–Scripting All Stored Procedures from All Databases with a command line.


Yesterday a friend asked to me if PowerShell could help him to script all Stored Procedures from All Databases in one of his servers. For some reason, the SSMS was taking too long to finish. In fact, until now, he still can not do it by GUI.

Another point is by the policies from his company , he needs to create a process to perform this operation every week.

I told him that we can do it with a command line. He thought I was lying and probably you too, so lets do it.

The first step was download the SQLPX (SQL Server PowerShell Extensions) and install in his desktop and be loaded in the profile. Then it is just type :

1 – To all stored procedures from one Database :

Get-SqlDatabase -sqlserver YourServer -Dbname MyDatabase | Get-SqlStoredProcedure | Get-Sqlscripter | out-file c:\temp\MyDatabase_StoredProcedures.sql

2 – To all stored procedures from ALL databases. It will create a .sql file with all stored procedures for each database.Ha make no mistake, this is a command line.

Get-SqlDatabase -sqlserver YourServer | 

Get-SqlStoredProcedure | 

ForEach-Object {

    $Database = $_.dbname

    Get-Sqlscripter  -smo $_  | 

    Out-File "c:\temp\Script\$($Database).sql" -Append

}

3  – If you want the “if not exists”, lets use the scripting options. Ok now I confess, it  is not  a command line….humm… three.

$scriptingOptions = New-SqlScriptingOptions

$scriptingOptions.IncludeIfNotExists = $true

Get-SqlDatabase -sqlserver YourServer  | 

Get-SqlStoredProcedure | 

ForEach-Object {

    $Database = $_.dbname

    Get-Sqlscripter  -smo $_ -scriptingOptions $scriptingOptions | 

    Out-File "c:\temp\Script\$($Database).sql" -Append

}

Now it is just create a SQL Server Agent Job and run this script.

Well, I can say that when he started  the process and finished some minutes after, the only word I heard was. “HOLY  *&¨%$%¨%$#$#%…..” ..well you can imagine the rest Alegre

#PowerShellLifeSTyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

1 Response to Quick Post–Scripting All Stored Procedures from All Databases with a command line.

  1. Pingback: Listing Database Info with Object counts | $hell Your Experience !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s