Month: June 2018

#******************************************************************
# POWERSCRIPT FILE NAME: getddl.ps1
# Scripts out DDL for given list of database objects
#******************************************************************
set-strictmode -version Latest;
#set-strictmode -off;

######USER PROVIDED VALUES#######
$SERVER_NAME = "MYSERVERNAME\MYINST";             #INSTANCE NAME
$DATABASE_NAME = "master";                        #DATABASE NAME
$OBJ_LIST = ('sysobjects','sp_who');              #PROVIDE LIST OF OBJECT NAMES IN ARRAY, EXAMPLE
#$OBJ_LIST = Get-Content "C:\temp\objects.txt";   #PROVIDE LIST OF OBJECT NAMES IN TEXT FILE, ONE ENTRY PER LINE
$OUT_PATH = "C:\temp";                            #OUTPUT FOLDER, MUST ALREADY EXIST


##GLOBAL VARIABLES##
$C_OK = 0;    # 0 for OK exit status
$C_NOTOK = 1; # Non-Zero for NOT-OK exit status


function main()
{
   $retstat = $C_OK;

   $tstamp = timestamp;
   write-host "$tstamp starting up";

   #connection string
   $CONN_STRING = "Server=$SERVER_NAME;Database=$DATABASE_NAME;Trusted_Connection=True";

   #initialize connection objects
   $SqlConn = New-Object System.Data.SQLClient.SQLConnection;
   $SqlConn.ConnectionString = $CONN_STRING;
   $SqlCmd = New-Object System.Data.SQLClient.SQLCommand;
   $SqlCmd.Connection = $SqlConn;

   #define command
   $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure;
   $SqlCmd.CommandText = 'sp_helptext';

   #define parameters
   $SqlCmd.Parameters.Add("@objname",[system.data.SqlDbType]::NVarChar) | out-Null;
   $SqlCmd.Parameters['@objname'].Direction = [system.data.ParameterDirection]::Input;

    
   try
   {
      $tstamp = timestamp;
      write-host "$tstamp connecting to $CONN_STRING";
      $SqlConn.Open();

      $tstamp = timestamp;
      write-host "$tstamp processing records";
      $n = 0;

      foreach ($obj in $OBJ_LIST)
      {
         $SqlCmd.Parameters['@objname'].value = clnStr $obj;
           
         #cmdToString $SqlCmd; ##output SQL command 
         $rs = $SqlCmd.ExecuteReader();

         $outfile = "$OUT_PATH\$obj.sql";

         $tstamp = timestamp;
         write-host "$tstamp Collecting $obj";

         $s = '';
         while ($rs.Read())
         {
            $s += $rs.GetValue(0);
         }
         $rs.Close();
         $rs.Dispose();

         $tstamp = timestamp;
         write-host "$tstamp Writing $outfile";
         $sw = new-object system.IO.StreamWriter($outfile);
         $sw.write($s);
         $sw.close();

         $n++;
      }#foreach
    
      $SqlCmd.Dispose();
      $SqlConn.Close();
      $SqlConn.Dispose();    

      $tstamp = timestamp;
      write-host "$tstamp processed $n objects";
   }
   catch
   {
      $line = $_.InvocationInfo.ScriptLineNumber
      $e = $_.Exception
      Write-Host -ForegroundColor Red "Exception: $e at line $line"
      #echo $_.Exception|format-list -force;  ##verbose error message
      $retstat = $C_NOTOK;
      cmdToString $SqlCmd;
   }
   
   $tstamp = timestamp;
   write-host "$tstamp shutting down";    
   exit $retstat;
}#main()


function timestamp()
{
   return($(get-date -F "yyyyMMdd_HHmmss"));
}#timestamp()



function clnStr( $val )
{
   if ( $($val) -eq $null )
   {
      $ret = [System.DBNull]::Value;
   }
   else
   {
      $ret = $val.toString().replace("'","''");
   }
   return($ret);
}#clnStr()



function cmdToString($cmd)
{
   $c = " ";
   $s = "EXEC " + $cmd.CommandText;
   foreach ($p in $cmd.Parameters)
   {
      $s += $c + $p.ParameterName;
      if ( $p.Value.Equals([DBNull]::Value) )
      {
         $s += " = NULL";
      }
      else
      {
         $s += " = '" + $p.Value + "'";
      }
      $c = ", ";
   }
   write-host $s;
}#cmdToString()


clear;
main;