How to retrieve all the stored procedures in a specified database

Introduction

This PowerShell script will help us get the codes of stored procedures in a specified database.  

Scenarios

A lot of people asked for methods to get the codes of stored procedures in a specified database. Some people want to save the codes so that they can run these codes in other databases.  

Script

You can use this script in this way: 
1. Open the script and replace the basic information of server and database with yours. Then save the script.
For example:
$conn = New-Object System.Data.SqlClient.SqlConnection "Server=Julie;Database=Test;Integrated Security=SSPI;";

2. Run Microsoft PowerShell as Administrator

3. Run the script in the form: Path
For example: E:\GetAllProcedures.ps1

4. Press Enter. After the script finishes running, we’ll get the following figure:


Here are some code snippets for your reference:

$conn = New-Object System.Data.SqlClient.SqlConnection 
 
"Server=Julie;Database=Test;Integrated Security=SSPI;"; 
 
## Attach the InfoMessage Event Handler to the connection to print the messages 
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) 
 
Write-Host $event.Message }; 
$conn.add_InfoMessage($handler); 
$conn.FireInfoMessageEventOnUserErrors = $true;
 
$conn.Open();
 
$cmd = $conn.CreateCommand();

$conn = New-Object System.Data.SqlClient.SqlConnection  
  
"Server=Julie;Database=Test;Integrated Security=SSPI;";  
  
## Attach the InfoMessage Event Handler to the connection to print the messages  
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event)  
  
Write-Host $event.Message };  
$conn.add_InfoMessage($handler);  
$conn.FireInfoMessageEventOnUserErrors = $true; 
  
$conn.Open(); 
  
$cmd = $conn.CreateCommand();

Prerequisites

SQL Server 2005 /SQL Server 2008/SQL Server 2008 R2/SQL Server 2012