# encoding: ascii
# api: powershell
# title: Script-Proc.sql
# description: A script that scripts a stored proc in SQL server.
# version: 0.1
# author: Justin Dearing
# license: CC0
# x-poshcode-id: 2580
# x-archived: 2015-05-06T16:09:38
# x-published: 2011-03-26T06:01:00
#
#
param(
    [string] $ProcedureName,
    [string] $Path = "$($ProcedureName).sql",
    [string] $ConnectionString = 'Data Source=.\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;'
);

try {
    [System.Data.SqlClient.SqlConnection] $cn = New-Object System.Data.SqlClient.SqlConnection (,$ConnectionString);
    $cn.Open() > $null;
    $cmd = $cn.CreateCommand();
    $cmd.CommandType = [System.Data.CommandType]::StoredProcedure;
    $cmd.CommandText = 'sp_helptext';
    $cmd.Parameters.AddWithValue('@objname', $ProcedureName) > $null;
    [System.Data.IDataReader] $rdr = $cmd.ExecuteReader();
    [string] $sproc_text = '';
    while ($rdr.Read()) {
        $sproc_text += $rdr[0];
    }
    $sproc_text | Out-File -FilePath $Path;
}
finally {
    if ($cmd -ne $null) { $cmd.Dispose(); }
    if ($cn -ne $null) { $cn.Dispose(); }
}
