Category Archives: PowerShell

Functions and routines I use often.

Creating a Table change script with PowerShell

In this example the scripts being built implement the table change pattern where you need to recreate the table.  In other words, the table requires changes that cannot be made with Alter commands.  One must create a new table, copy the old content into the new table, rename ORIGINAL to OLD and NEW to ORIGINAL.

In this scenario the original is kept because a RollBack script is also created.  That script renames the current to new and puts the old version back into place.  In the case of both scripts, they are repeatable.  That is, the scripts detect the state by the absence or presence of the OLD (rollback) table.  Then proceed accordingly.

To make this a rich example of how to do a lot of things I try to support all kinds of scenarios. Some may not be implemented in a way that works in your environment but all these work is the large and complex environment in which they were developed so it should make a good start. It should also give a working example of how to do many things associated with creating scripts in a Microsoft SQL Server environment using PowerShell.

Upon execution of the script the old table structure is simply duplicated in the new table and the content copied verbatim. You still need to make whatever changes are required and fix up the copy statement if necessary but it can still save a HUGE amount of work.

This puts all the original constraints, keys and triggers on your new table as well as moving foreign key relationships from the (now) backup copy to the new table. It even gives you boilerplate code to deal with replication and CDC if they apply. All you need to do is add your changes!
 

<#
  .SYNOPSIS
    Creates a script (and its rollback) to alter a table via the RENAME/COPY method.
	Use New-AlterTableScript2 when you will just use ALTER to change the table.

  .Description
    Creates 2 scripts.  One to create a temp table identical to the requested table,
    copy the original content to the new table, rename the original to a _DeleteMe version,
    then rename the copy into production.  It handles such things as renaming triggers,
    column constraints and indexes as well as dropping and appropriately re-creating foreign
    key constraints so the new table is identical to the old.  It also creates a rollback
    script to delete the new table and re-install the _DeleteMe version.  
    You must make any changes to the new version yourself but the boiler-plate code can be
    significant so it can save a ton of work.

    In general quotes are always allowed around paramter values but only required if the parameter 
    contains certain special characters.

  .Parameter TableName
    Required.  The name of the table upon which to operate.  The table must exist on the instance
    database and schema specified in the remaining parameters.

  .Parameter StoryNumber
    One of {US|BUG|INC} followed by a 6 digit number. For example US203045. Defaults to USnnnnnn.

  .Parameter StepNumber
    This is used to sequence the scripts that belong to a story.  4 digit max!  Defaults to 9999

  .Parameter ProjectAbbreviation
    A 2 character project identifier.  Typically something like CS, PR, PT.  Defaults to PR

  .Parameter Operation
    Very short 1-word description of the type of change.  Examples: AddPk, Convert2Seq

  .Parameter DatabaseName
    The name of the database within the instace that contains the object table
    Defaults to PowerTrack

  .Parameter SchemaName
    Used to identify the schema of the procedure being changed.  Defaults to dbo.

  .Parameter InstanceName
    Defaults to .

  .Parameter BuildVersion
    A string of the form yyyy-mm that defaults to the current month if DOM <=17 otherwise
    it defaults to next month.

  .Inputs
    Pipeline input not supported

  .Outputs
    SQL scripts for switching the old table for a new one and another script to roll back

  .Example
    New-AlterTableScript PR_TradingPartners US192023 10

    Builds a copy-rename script and rollback script for replacing PR_TradingPartners from Dev4 data

    US192023-0010-R2-PR-AlterTable-PR_TradingPartners-Rollback.sql 
    US192023-0010-R2-PR-AlterTable-PR_TradingPartners.sql          

  .Example
    New-AlterTableScript UI_ContentContainer US192023 10 CS '' UIFramework dbo 0

    Builds a copy-rename script and rollback script for replacing UI_ContentContainer in the DEV0 environment.

  .Example
    New-AlterTableScript -TableName 'PR_TradingPartners' -StoryNumber 'US192023' -StepNumber 10 

    Builds the following scripts:
    US192023-0010-R2-PR-AlterTable-PR_TradingPartners.sql
    US192023-0010-R2-PR-AlterTable-PR_TradingPartners-Rollback.sql

  .Notes
    Author: Rick Bielawski
#>
function New-AlterTableScript {
[CmdLetBinding()]
param([parameter(HelpMessage='The name of the table to be altered.'
                ,Mandatory=$true)]
         [string]$TableName
     ,[parameter(HelpMessage='The story number for which the script is being created. Defaults to "USnnnnnn"')]
         [string]$StoryNumber = 'USnnnnnn'
     ,[parameter(HelpMessage='Step number for script sequencing. Defaults to "9999"')]
         [ValidateRange(0,9999)]
         [int]$StepNumber = 9999
     ,[parameter(HelpMessage='Two letter abbreviation of the project name. Defaults to "PR"')]
         [string]$ProjectAbbreviation = 'PR'
     ,[parameter(HelpMessage='Database name for metadata source. Defaults to "Powertrack"')]
         [string]$DatabaseName = 'PowerTrack'
     ,[parameter(HelpMessage='Specify the schema name when other than the default "dbo"')]
         # I didn't have any other good place to put an example of ValidateSet.  Uncomment to try it!
         #[ValidateSet('dbo', 'Replicated', 'Archive')]  
         [string]$SchemaName = 'dbo'
     ,[parameter(HelpMessage='Identifies the instance of SQL Server from which to retrieve data.')]
         [string]$InstanceName = '.'
     ,[parameter(HelpMessage='YYYY-MM format date of sprint build. Defaults to current month if < 16th else next month')]
         [string]$BuildVersion = $(if ((Get-Date).Day -le 17){Get-Date -UFormat '%Y-%m'}else{(Get-Date).AddMonths(1).ToString('yyyy-MM')})
     )
<#
         [string]$SchemaName          = 'dbo'
         [string]$TableName           = 'BR_DecisionPoint'
         [string]$StoryNumber         = 'S207359'
         [int]   $StepNumber          = 10
         [string]$ProjectAbbreviation = 'er'
         [string]$DatabaseName        = 'Rick'
         [string]$BuildVersion        = $(if ((Get-Date).Day -le 17){Get-Date -UFormat '%Y-%m'}else{(Get-Date).AddMonths(1).ToString('yyyy-MM')})
#>


function Get-QueryResults ($Query) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Server=$InstanceName;Database=$($Db.Name);Integrated Security=True" 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.Connection = $SqlConnection 
    $SqlCmd.CommandText = $Query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet 
    $SqlAdapter.Fill($DataSet) > $null
    $SqlConnection.Close() 
    $DataSet.Tables[0]
} 


$Smo         = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
$Srv         = New-Object "Microsoft.SqlServer.Management.Smo.Server" $InstanceName
               IF ($Srv -eq $null) {throw "Instance not found:$InstanceName"}
               # We can't discover the true case of object names if $Srv.Databases[$DatabaseName] syntax is used
$Db          = $Srv.Databases|?{$_.name -eq $DatabaseName}
               IF ($Db -eq $null) {throw "Database not found:$DatabaseName"}
$Tbl         = $Db.Tables|?{$_.Name -eq $TableName -and $_.Schema -eq $SchemaName}
               IF ($Tbl -eq $null) {throw "Table not found:$SchemaName.$TableName"}
$Scripter    = new-object "Microsoft.SqlServer.Management.Smo.Scripter" $Srv; 

If($BuildVersion -eq $null) {$BuildVersion = $(if ((Get-Date).Day -le 17){Get-Date -UFormat '%Y-%m'}
                                             else{(Get-Date).AddMonths(1).ToString('yyyy-MM')})}
$UserId                = $env:USERNAME
$ErrorActionPreference = "continue"
$SchemaName            = $Tbl.Schema
$TableName             = $Tbl.Name
$DatabaseName          = $db.Name
$ProjectAbbreviation   = $ProjectAbbreviation.ToUpper()
$StoryNumber           = $StoryNumber.ToUpper()
$StepNumberFormatted   = $($StepNumber.ToString('0000'))
$ChangeComment         = "$StoryNumber-$StepNumberFormatted-$InstanceID-$ProjectAbbreviation-AlterTable-$TableName"
$ChangeFilename        = $ChangeComment+'.sql'
$RollBackFilename      = $ChangeComment+'-Rollback.sql'

$Uniquifier     = "_$($StoryNumber)_$($StepNumberFormatted)_DeleteMe"
$TmpTblName     = "Tmp_$TableName"
$FullTmpTblName = "$SchemaName.$TmpTblName"
$FullTblName    = "$SchemaName.$TableName"
$FullUniqueName = "$SchemaName.[$TableName$Uniquifier]"

$PrimaryKey = $tbl.Indexes|?{$_.IndexKeyType -eq 'DriPrimaryKey'}
$UniqueKeys = $tbl.Indexes|?{$_.IndexKeyType -eq 'DriUniqueKey'}
$HasIdentity= $Tbl.Columns|%{$_.Identity}|?{$_}
$AllColumns = $Tbl.Columns|%{$_.Name}
$CommaSeparatedColumns = [string]::Join(', ',$AllColumns)


$FKeyList = Get-QueryResults(
"select fk.parent_object_id,fk.object_id
from sys.foreign_keys fk
where fk.referenced_object_id= object_id('$FullTblName')")
$ForeignKeys = $FKeyList|
    ?{$_.parent_object_id -ne $Tbl.ID}|
    %{$db.Tables.ItemById($_.Parent_Object_Id).ForeignKeys.ItemById($_.Object_Id)}


$DefaultConstraints = $tbl.Columns|
    ?{$_.DefaultConstraint}|
    ?{$_.DefaultConstraint.Name -ne "DF_$($TableName)_$($_.Name)"}|
    %{"   IF OBJECT_ID(      '$SchemaName.$($_.DefaultConstraint.Name)') IS NOT NULL
       exec sp_rename '$SchemaName.$($_.DefaultConstraint.Name)'
                         ,'DF_$($TableName)_$($_.Name)$Uniquifier','OBJECT'
"}
$DefaultConstraints += $tbl.Columns|
    ?{$_.DefaultConstraint}|
    %{"   IF OBJECT_ID(      '$SchemaName.DF_$($TableName)_$($_.Name)') IS NOT NULL
       exec sp_rename '$SchemaName.DF_$($TableName)_$($_.Name)'
                         ,'DF_$($TableName)_$($_.Name)$Uniquifier','OBJECT'
"}


$Publication = Get-QueryResults("
  use $Db;
  if (OBJECT_ID('dbo.sysarticles') IS NOT NULL
  and OBJECT_ID('dbo.syspublications') IS NOT NULL)
    select *
      from dbo.sysarticles sa
      join dbo.syspublications sp
        on sp.pubid = sa.pubid 
     where sa.objid = object_id('$FullTblName');")

$CDC = Get-QueryResults("
if (select is_tracked_by_cdc from sys.tables where object_id=OBJECT_ID('$FullTblName')) = 1
    exec sys.sp_cdc_help_change_data_capture '$SchemaName','$TableName';")


FUNCTION GenerateChange {

"-- $ChangeFilename
USE $DatabaseName;
GO

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON

IF  OBJECT_ID('$FullTmpTblName') IS NULL
AND OBJECT_ID('$FullTblName$Uniquifier') is null
BEGIN
  BEGIN TRANSACTION
"

'-- Default Constraints'
if ($DefaultConstraints) {
  "    -- Rename default constraints so new table can use the same names."
  $DefaultConstraints
}

'-- Check Constraints'
if ($tbl.Checks -ne $null -and $tbl.Checks.Count -gt 0) {
  "    -- Rename check constraints so we can give new table the same names."
  $tbl.Checks|
    %{"   IF OBJECT_ID(      '$SchemaName.$($_.Name)') IS NOT NULL
       exec sp_rename '$SchemaName.$($_.Name)'
                         ,'$($_.Name)$Uniquifier','OBJECT'
"    }
}

'-- Create table'
# Create the new table.  Get rid of brackets around names since we should not need them and they make the script harder to read
$Scripter.Options.NoCollation = $true
$Scripter.Options.Permissions = $true
$Scripter.Options.DriChecks   = $true
$TableCreation = $scripter.script($tbl).
                           replace('[','').
                           replace(']','').
                           replace('ON PRIMARY','').
                           Replace("`t",'    ').
                           Replace("`n",'').
                           replace($FullTblName,$FullTmpTblName).
                           Split("`r",$null,[System.StringSplitOptions].SingleLine)+';'
$TableCreation|
    ?{$_ -notlike '*SET (LOCK_ESCALATION = AUTO)*'}|
    ?{$_ -notlike ''}|
    %{"    $_"}

# If there were default constraints on the old table recreate them on the new table

IF ($DefaultConstraints) {'
-- Default Constraints'
    $tbl.Columns|
      ?{$_.DefaultConstraint}|
      %{"    ALTER TABLE $FullTmpTblName ADD CONSTRAINT"
        '                '+(' '*$SchemaName.Length)+"  DF_$($TableName)_$($_.Name) DEFAULT $($_.DefaultConstraint.Text) FOR "
        ' '*$FullTmpTblName.Length +"                 $($_.Name);
"      }
}

# Copy the original table content to the new table
'-- Copy data'
"    ALTER TABLE $FullTmpTblName SET (LOCK_ESCALATION = TABLE);
     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
$(&{if ($HasIdentity){`"    SET IDENTITY_INSERT $FullTmpTblName ON;`"}})
    INSERT INTO $FullTmpTblName 
          ($CommaSeparatedColumns)
    SELECT $CommaSeparatedColumns
      FROM $FullTblName WITH (HOLDLOCK TABLOCKX);
$(&{if ($HasIdentity){`"    SET IDENTITY_INSERT $FullTmpTblName OFF;`"}})
    ALTER TABLE $FullTmpTblName SET (LOCK_ESCALATION = AUTO);
"

# Foreign key constraints need to be dropped from the old table and added onto the new table
'-- Incoming Foreign Keys'
$ForeignKeys|
    %{"
    IF OBJECT_ID(  '$($_.Parent.Schema).$($_.Name)') IS NOT NULL
      ALTER TABLE   $($_.Parent.Schema).   $($_.Parent.Name)
        DROP CONSTRAINT $($_.Name);"
     }

# Rename table specific dependent objects with global names so those names can be used for objects on the new table
'
-- Outgoing Foreign Keys'
$Tbl.ForeignKeys|
    %{"
    IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)') IS NOT NULL
        Exec sp_rename '$($_.Parent.Schema).$($_.Name)'
                         , '$($_.Name)$Uniquifier';"
     }

'-- Primary Key'
$PrimaryKey|
    %{"
    IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)') IS NOT NULL
        Exec sp_rename '$($_.Parent.Schema).$($_.Name)'
                         , '$($_.Name)$Uniquifier';"
     }

'-- Unique keys'
$UniqueKeys|
    %{"
    IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)') IS NOT NULL
        Exec sp_rename '$($_.Parent.Schema).$($_.Name)'
                         , '$($_.Name)$Uniquifier';"
     }

'-- Triggers'
$tbl.Triggers|
    %{"
    IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)') IS NOT NULL
        Exec sp_rename '$($_.Parent.Schema).$($_.Name)'
                         , '$($_.Name)$Uniquifier';"
     }

IF ($CDC -ne $null) {
"
-- CDC was detected. *** WARNING - EXECUTING THIS CAN HAVE SEVERE DOWNSTREAM IMPLICATIONS TO CDC READERS ***

if (select is_tracked_by_cdc from sys.tables where object_id=OBJECT_ID('$FullTblName')) = 1
        EXEC sys.sp_cdc_disable_table '$SchemaName','$TableName','all';
    "
}

'-- Replication pre-rename'
IF ($Publication) {
"
  COMMIT TRANSACTION
END

IF  OBJECT_ID('$FullTblName$Uniquifier') is null
BEGIN
  BEGIN TRANSACTION
    -- This is a replicated object which can't be renamed while part of replication
    DECLARE @pubName sysname = N'$($Publication.Name1)';
    DECLARE @source_owner sysname = N'$($tbl.Schema)';
    DECLARE @source_object sysname = N'$($tbl.Name)';
    
    --Drop it if the Article exists
    IF EXISTS (SELECT * FROM dbo.sysarticles WHERE dest_table = @source_object 
                  and pubid = (SELECT pubID FROM [dbo].[syspublications] WHERE [name] = @pubName))
    BEGIN
        EXEC sp_dropsubscription 
        	  @publication = @pubName
        	, @article = @source_object
            , @subscriber = 'all';
        PRINT 'Subscriptions removed from Article';
        
        EXEC sp_droparticle 
        	  @publication = @pubName
        	, @article = @source_object
        	, @force_invalidate_snapshot= 1;
        PRINT 'Article Dropped.';
    END -- IF Article exists
"}

"
-- Table Renames
    IF  OBJECT_ID(      '$FullTblName') IS NOT NULL
                                     AND OBJECT_ID('$FullTblName$Uniquifier') IS NULL
        EXEC sp_rename N'$FullTblName'    , N'$TableName$Uniquifier', 'OBJECT' 
    
    IF  OBJECT_ID(      '$FullTmpTblName') IS NOT NULL
                                     AND OBJECT_ID('$FullTblName') IS NULL
        EXEC sp_rename N'$FullTmpTblName', N'$TableName', 'OBJECT' 
"
if ($PrimaryKey -ne $null) {
  $PKCreation = $scripter.script($PrimaryKey).
                           Replace('[','').
                           Replace(']','').
                           Replace("`t",'    ').
                           Replace("`n",'').
                           Split("`r",$null,[System.StringSplitOptions].SingleLine)|
                  ?{$_ -notlike 'SET *'}|
                  ?{$_ -ne ''}|
                  %{"        $_"}

  '-- New table PK'
  "    IF OBJECT_ID('$($tbl.Schema).$($PrimaryKey.Name)') IS NULL"
  $([string]::Join("`n",($PKCreation))).Replace("`r","`n").Replace("`n`n","`n").Replace("`n","`r`n")
}

'-- Replication post-rename'
IF ($Publication) {
"
    --Make sure that the publication exists
    IF EXISTS (SELECT * FROM [dbo].[syspublications] WHERE [name] = @pubName)
    BEGIN
        IF NOT EXISTS (SELECT * FROM dbo.sysarticles WHERE dest_table = @source_object 
                      and pubid = (SELECT pubID FROM [dbo].[syspublications] WHERE [name] = @pubName))
    	BEGIN
    		PRINT	'Calling: sp_addarticle | ' + 'publication: ' + @pubName + ' | article: ' + @source_object;
    
    		EXEC sp_addarticle 
    			  @publication = @pubName
    			, @article = @source_object
    			, @source_owner = @source_owner
    			, @source_object = @source_object
    			, @type = N'logbased'    $(if ($Publication.type -ne 1){'<<-- THIS VALUE IS WRONG!!!!! FIX IT!!!!'})
    			, @description = $(if ($Publication.description -is [dbnull]) {'null'}
                                       else{"N'$($Publication.description.Replace("'","''"))'"})                                     -- Set by Powershell
    			, @creation_script = $(if ($Publication.creation_script -is [dbnull]) {'null'}
                                       else{"N'$($Publication.creation_script.Replace("'","''"))'"})                                 -- Set by Powershell
    			, @pre_creation_cmd = N'$(switch ($Publication.pre_creation_cmd) {
                                              0 {'None'} 
                                              1 {'DROP'} 
                                              2 {'DELETE'} 
                                              3 {'TRUNCATE'}})'                             -- Set by Powershell
    			, @schema_option = 0x$(($Publication.schema_option|foreach-object ToString X2) -join '')                     -- Set by Powershell
    			, @identityrangemanagementoption = N'manual'       -- Hard coded in Powershell, could be wrong
    			, @destination_table = @source_object
    			, @destination_owner = @source_owner	
    			, @status = $($Publication.status -band 24)                                            -- Set by Powershell
    			, @vertical_partition = N'false'
    			, @ins_cmd = $(if ($Publication.ins_cmd -is [dbnull]) {'null'}
                                       else{"N'$($Publication.ins_cmd.Replace("'","''"))'"}) -- Set by Powershell
    			, @del_cmd = $(if ($Publication.del_cmd -is [dbnull]) {'null'}
                                       else{"N'$($Publication.del_cmd.Replace("'","''"))'"}) -- Set by Powershell
    			, @upd_cmd = $(if ($Publication.upd_cmd -is [dbnull]) {'null'}
                                       else{"N'$($Publication.upd_cmd.Replace("'","''"))'"}) -- Set by Powershell
    			, @force_invalidate_snapshot=1;                    -- Hard coded in Powershell, could be wrong
    		PRINT 'Article added.';
    
    		-- REFRESH SUBSCRIPTIONS
    		PRINT	'Calling: sp_refreshsubscriptions | ' + 'publication: ' + @pubName;
    		EXEC sp_refreshsubscriptions @publication = @pubName;
    
    	END
    	ELSE
    		PRINT 'The object ' + @source_object + ' has already been added.';
    END
    ELSE
    BEGIN
    	DECLARE @msg nvarchar(1000) = 'Error: [' + @pubName + '] Publication does NOT Exist.';
    	RAISERROR(@msg, 11, 1);
    END
"}

            # NOTICE: $CDC contains everything you need to rebuild CDC Properly.
            #         The connections were deleted with 'all' and more than one row may exist requiring multiple enable commands
IF ($CDC -ne $null) {
"
-- CDC was detected. *** WARNING - THE FOLLOWING code is UNRELIABLE *** It was NOT built from a query of existing setup!!!
-- use sys.sp_cdc_help_change_data_capture to check the existing setup and modify this section to re-enable CDC Properly

if (select is_tracked_by_cdc from sys.tables where object_id=OBJECT_ID('$FullTblName')) = 0
        EXEC sys.sp_cdc_enable_table  @source_schema = N'$SchemaName',@source_name= N'$TableName',@role_Name=null;
    "
}


 # Surround all the index builds with IF in case they are built already.
'
-- Indexes'
$tbl.Indexes|
    ?{$_.IndexKeyType -ne 'DriPrimaryKey'}|
    %{"If IndexProperty(Object_Id('$FullTblName'), '$($_.Name)', 'IndexId') IS NULL"
      $([string]::Join("`r`n",($scripter.script($_).
                                      replace('[','').
                                      replace(']','').
                                      Replace("`t",'    ').
                                      Replace("`n",'').
                                      Split("`r",$null,[System.StringSplitOptions].SingleLine)|
                             ?{$_ -notlike 'SET *'}|
                             ?{$_ -ne ''}|
                             %{"        $_"})))
     ''
    }

 # Recreate any triggers that don't exist.
'-- Triggers'
$Scripter.Options.IncludeIfNotExists                    = $true
$tbl.Triggers|
    %{$scripter.script($_);''}|?{$_ -notlike 'SET *'}

 # Recreate foreign keys
'-- Incoming Foreign Keys'
$ForeignKeys|
    %{$([string]::Join("`r`n",($scripter.script($_).Replace('[','').Replace(']','').Replace("`t",'    ').
                                       Replace("`r`n","`r").Replace("`n","`r").Replace("`r`r","`r").
                                       Replace("`rALTER ","`r    ALTER ").
                                       Replace("`rREFERENCES ","`r    REFERENCES ")|
                                       %{"$_`r".
                                       Split("`r",$null,[System.StringSplitOptions].SingleLine)})))
     }
'-- Outgoing Foreign Keys'
$Tbl.ForeignKeys|
    %{$([string]::Join("`r`n",($scripter.script($_).Replace('[','').Replace(']','').Replace("`t",'    ').
                                       Replace("`r`n","`r").Replace("`n","`r").Replace("`r`r","`r").
                                       Replace("`rALTER ","`r    ALTER ").
                                       Replace("`rREFERENCES ","`r    REFERENCES ")|
                                       %{"$_`r".
                                       Split("`r",$null,[System.StringSplitOptions].SingleLine)})))
     }
"  COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END

GO"
}


FUNCTION GenerateRollback {

"-- $RollbackFilename
USE $DatabaseName;
GO

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON

IF  OBJECT_ID('$FullTmpTblName') IS NOT NULL
    DROP TABLE $FullTmpTblName;

IF OBJECT_ID('$FullUniqueName') IS NOT NULL
BEGIN
    -- DON'T LOOSE DATA DURING ROLLBACK
  BEGIN TRY
      BEGIN TRANSACTION
          SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
          DECLARE @C1 BIGINT = (SELECT COUNT(*) FROM $FullUniqueName WITH(HOLDLOCK TABLOCKX));
          DECLARE @C2 BIGINT = (SELECT COUNT(*) FROM $FullTblName WITH(HOLDLOCK TABLOCKX));
          PRINT @C1
          PRINT @C2
          IF ( @C1 <> @C2)
          BEGIN
              PRINT 'COPYING'
              DELETE FROM         $FullUniqueName;
              ALTER TABLE         $FullUniqueName SET (LOCK_ESCALATION = TABLE);
              $(&{if ($HasIdentity){`"SET IDENTITY_INSERT $FullUniqueName ON;`"}})
              INSERT INTO $FullUniqueName
                     ($CommaSeparatedColumns)
              SELECT  $CommaSeparatedColumns
                FROM $FullTblName  WITH (HOLDLOCK TABLOCKX);
              $(&{if ($HasIdentity){`"SET IDENTITY_INSERT $FullUniqueName OFF;`"}})
              ALTER TABLE         $FullUniqueName SET (LOCK_ESCALATION = AUTO);
          END
      COMMIT TRANSACTION
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  END TRY
  BEGIN CATCH
      PRINT ERROR_MESSAGE();
      IF @@Trancount > 0 ROLLBACK TRANSACTION;
  END CATCH
"

 # Foreign key constraints need to be dropped from the old table and added onto the new table
'-- Drop foreign keys'
$ForeignKeys|
    %{"
    IF OBJECT_ID(  '$($_.Parent.Schema).$($_.Name)') IS NOT NULL
      ALTER TABLE   $($_.Parent.Schema).   $($_.Parent.Name)
        DROP CONSTRAINT $($_.Name);"
     }

IF ($CDC -ne $null) {
"
-- CDC was detected. *** WARNING - EXECUTING THIS CAN HAVE SEVERE DOWNSTREAM IMPLICATIONS TO CDC READERS ***

if (select is_tracked_by_cdc from sys.tables where object_id=OBJECT_ID('$FullTblName')) = 1
        EXEC sys.sp_cdc_disable_table '$SchemaName','$TableName','all';
    "
}


'-- Pre-rename replication'
IF ($Publication) {
"
    -- This is a replicated object which can't be renamed while part of replication
    DECLARE @pubName sysname = N'$($Publication.Name1)';
    DECLARE @source_object sysname = N'$($tbl.Name)';
    
    --Drop it if the Article exists
    IF EXISTS (SELECT * FROM dbo.sysarticles WHERE dest_table = @source_object 
                  and pubid = (SELECT pubID FROM [dbo].[syspublications] WHERE [name] = @pubName))
    BEGIN
        EXEC sp_dropsubscription 
        	  @publication = @pubName
        	, @article = @source_object
            , @subscriber = 'all';
        PRINT 'Subscriptions removed from Article';
        
        EXEC sp_droparticle 
        	  @publication = @pubName
        	, @article = @source_object
        	, @force_invalidate_snapshot= 1;
        PRINT 'Article Dropped.';
    END -- IF Article exists
"}

'-- Replace original table'
"
    IF  OBJECT_ID('$FullTblName') IS NOT NULL
        DROP TABLE $FullTblName;

    EXEC sp_rename N'$FullTblName$Uniquifier', N'$TableName', 'OBJECT' 
  END
GO
"

'-- Post-rename replication'
IF ($Publication) {
"
DECLARE @pubName sysname = N'$($Publication.Name1)';
DECLARE @source_owner sysname = N'$($tbl.Schema)';
DECLARE @source_object sysname = N'$($tbl.Name)';
          
--Make sure that the publication exists
IF EXISTS (SELECT * FROM [dbo].[syspublications] WHERE [name] = @pubName)
  BEGIN
    IF NOT EXISTS (SELECT * FROM dbo.sysarticles WHERE dest_table = @source_object 
                  and pubid = (SELECT pubID FROM [dbo].[syspublications] WHERE [name] = @pubName))
	  BEGIN
		PRINT	'Calling: sp_addarticle | ' + 'publication: ' + @pubName + ' | article: ' + @source_object;

		EXEC sp_addarticle 
			  @publication = @pubName
			, @article = @source_object
			, @source_owner = @source_owner
			, @source_object = @source_object
			, @type = N'logbased'    $(if ($Publication.type -ne 1){'<<-- THIS VALUE IS WRONG!!!!! FIX IT!!!!'})
			, @description = $(if ($Publication.description -is [dbnull]) {'null'}
                                   else{"N'$($Publication.description.Replace("'","''"))'"})                                     -- Set by Powershell
			, @creation_script = $(if ($Publication.creation_script -is [dbnull]) {'null'}
                                   else{"N'$($Publication.creation_script.Replace("'","''"))'"})                                 -- Set by Powershell
			, @pre_creation_cmd = N'$(switch ($Publication.pre_creation_cmd) {
                                          0 {'None'} 
                                          1 {'DROP'} 
                                          2 {'DELETE'} 
                                          3 {'TRUNCATE'}})'                             -- Set by Powershell
			, @schema_option = 0x$(($Publication.schema_option|foreach-object ToString X2) -join '')                     -- Set by Powershell
			, @identityrangemanagementoption = N'manual'       -- Hard coded in Powershell, could be wrong
			, @destination_table = @source_object
			, @destination_owner = @source_owner	
			, @status = $($Publication.status -band 24)                                            -- Set by Powershell
			, @vertical_partition = N'false'
			, @ins_cmd = $(if ($Publication.ins_cmd -is [dbnull]) {'null'}
                                   else{"N'$($Publication.ins_cmd.Replace("'","''"))'"}) -- Set by Powershell
			, @del_cmd = $(if ($Publication.del_cmd -is [dbnull]) {'null'}
                                   else{"N'$($Publication.del_cmd.Replace("'","''"))'"}) -- Set by Powershell
			, @upd_cmd = $(if ($Publication.upd_cmd -is [dbnull]) {'null'}
                                   else{"N'$($Publication.upd_cmd.Replace("'","''"))'"}) -- Set by Powershell
			, @force_invalidate_snapshot=1;                    -- Hard coded in Powershell, could be wrong
		PRINT 'Article added.';

		-- REFRESH SUBSCRIPTIONS
		PRINT	'Calling: sp_refreshsubscriptions | ' + 'publication: ' + @pubName;
		EXEC sp_refreshsubscriptions @publication = @pubName;

	  END
	ELSE
		PRINT 'The object ' + @source_object + ' has already been added.';
  END
ELSE
  BEGIN
	DECLARE @msg nvarchar(1000) = 'Error: [' + @pubName + '] Publication does NOT Exist.';
	RAISERROR(@msg, 11, 1);
  END
GO
"}

IF ($CDC -ne $null) {
"
-- CDC was detected. *** WARNING - THE FOLLOWING code is UNRELIABLE *** It was not built from a query of existing setup!!!
-- use sys.sp_cdc_help_change_data_capture to check the existing setup and modify this section to re-enable CDC Properly

if (select is_tracked_by_cdc from sys.tables where object_id=OBJECT_ID('$FullTblName')) = 0
        EXEC sys.sp_cdc_enable_table  @source_schema = N'$SchemaName',@source_name= N'$TableName',@role_Name=null;
    "
}

'--Recreate foreign keys'
$ForeignKeys|
    %{$([string]::Join("`r`n",($scripter.script($_).Replace('[','').Replace(']','').Replace("`t",'    ').
                                       Replace("`r`n","`r").Replace("`n","`r").Replace("`r`r","`r").
                                       Replace("`rALTER ","`r    ALTER ").
                                       Replace("`rREFERENCES ","`r    REFERENCES ")|
                                       %{"$_`r".
                                       Split("`r",$null,[System.StringSplitOptions].SingleLine)})))
     }

'--Rename check constraints'
$tbl.Checks|
    %{"
IF OBJECT_ID(      '$SchemaName.$($_.Name)$Uniquifier') IS NOT NULL
    exec sp_rename '$SchemaName.$($_.Name)$Uniquifier'
                      ,'$($_.Name)','OBJECT'
"}


'--Rename default constraints'
$tbl.Columns|
    ?{$_.DefaultConstraint}|
    %{"
IF OBJECT_ID(      '$SchemaName.DF_$($TableName)_$($_.Name)$Uniquifier') IS NOT NULL
    exec sp_rename '$SchemaName.DF_$($TableName)_$($_.Name)$Uniquifier'
                      ,'DF_$($TableName)_$($_.Name)','OBJECT';
"
     }

'--Rename PK'
$PrimaryKey|
    %{"
IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)$Uniquifier') IS NOT NULL
    Exec sp_rename '$($_.Parent.Schema).$($_.Name)$Uniquifier'
                     , '$($_.Name)';"
     }

'--Rename FKs'
$Tbl.ForeignKeys|
    %{"
IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)$Uniquifier') IS NOT NULL
    Exec sp_rename '$($_.Parent.Schema).$($_.Name)$Uniquifier'
                     , '$($_.Name)';"
     }

'--Rename Unique keys'
$UniqueKeys|
    %{"
IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)$Uniquifier') IS NOT NULL
    Exec sp_rename '$($_.Parent.Schema).$($_.Name)$Uniquifier'
                     , '$($_.Name)';"
     }

'--Rename triggers'
$tbl.Triggers|
    %{"
IF OBJECT_ID(      '$($_.Parent.Schema).$($_.Name)$Uniquifier') IS NOT NULL
    Exec sp_rename '$($_.Parent.Schema).$($_.Name)$Uniquifier'
                     , '$($_.Name)';"
     }
"
GO"
}

# If you're in a release folder try to put the respective scripts in their conventional places.  

# IOW if a Main folder exists in your path look for a path with Rollback in the same location and put Rollback files there.

$Location = (Get-Location).Path

if ($Location -like '*\Rollback\*') {$Location = $Location.Replace('\Rollback\','\Main\')}
GenerateChange > (Join-Path $Location $ChangeFilename)
Join-Path $Location $ChangeFilename

if ($Location -like '*\Main\*') {$Location = $Location.Replace('\Main\','\Rollback\')}
GenerateRollback > (Join-Path $Location $RollBackFilename)
Join-Path $Location $RollBackFilename
}

Converting netstats output to a stream of PowerShell objects

The netstats command returns useful information that isn’t easy to collect using PowerShell functions directly.  Unfortunately using it’s output isn’t straightforward because the data isn’t completely columnar.  That is, some data ‘columns’ are returned on their own separate lines, making the data difficult to use.  This creates custom objects that are easily consumed by other PowerShell operators.

function Get-NetStatData ([switch]$Numeric)
{
    function Get-EmptyRow {''|select -Property Protocol,LocalAddress,TargetAddress,State,PID,Program,OnBehalfOf}
    $Params = $(if ($Numeric) {'-abno'} else {'-abo'})
    $ObjectRow = Get-EmptyRow

    switch -Regex(netstat $Params)
    {'^ (?<pgm>[^ ].+)$' # begins with only 1 space (owner program)
        {$ObjectRow.Program = $Matches['pgm']
        }
     '^  (?<of>\w+)$'   # starts with 2 spaces then just 1 word (caller of owner?)
        {$ObjectRow.OnBehalfOf = $Matches['of']
        }
     '^  (?<proto>\w+)  +(?<local>[^ ]+)  +(?<target>[^ ]+)  +(?<state>\w+)?  +(?<pid>\d+)$' # Main data line
        {if ($ObjectRow.LocalAddress -ne $null)
            {$ObjectRow
             $ObjectRow = Get-EmptyRow
            }
         $ObjectRow.Protocol = $Matches['proto']
         $ObjectRow.LocalAddress = $Matches['local']
         $ObjectRow.TargetAddress = $Matches['target']
         $ObjectRow.State = $Matches['state']
         $ObjectRow.PID = $Matches['pid']
        }
     default {} # ignore empty lines and the initial "Active Connections" line (which doesn't start with a space)
    }
    $ObjectRow
}
Get-NetStatData |ft 

Protocol LocalAddress                         TargetAddress               State       PID   Program                              OnBehalfOf       
-------- ------------                         -------------               -----       ---   -------                              ----------       
TCP      0.0.0.0:80                           LocalLaptopJ6K:0            LISTENING   4     Can not obtain ownership information                  
TCP      0.0.0.0:135                          LocalLaptopJ6K:0            LISTENING   536   [svchost.exe]                        RpcSs            
TCP      0.0.0.0:445                          LocalLaptopJ6K:0            LISTENING   4     Can not obtain ownership information                  
TCP      0.0.0.0:2179                         LocalLaptopJ6K:0            LISTENING   4228  [vmms.exe]                                            
TCP      0.0.0.0:2383                         LocalLaptopJ6K:0            LISTENING   5512  [msmdsrv.exe]                                         
TCP      0.0.0.0:2701                         LocalLaptopJ6K:0            LISTENING   14948 [CmRcService.exe]                                     
TCP      0.0.0.0:3389                         LocalLaptopJ6K:0            LISTENING   1300  [svchost.exe]                        TermService      
TCP      0.0.0.0:5120                         LocalLaptopJ6K:0            LISTENING   4128  [STSchedEx.exe]                                       
UDP      0.0.0.0:123                          *:*                                     1444  [svchost.exe]                        W32Time          
UDP      0.0.0.0:500                          *:*                                     3872  [svchost.exe]                        IKEEXT           
UDP      0.0.0.0:1434                         *:*                                     3528  [sqlbrowser.exe]                                      
UDP      0.0.0.0:3249                         *:*                                     17152 [msddsk.exe]                                          
UDP      0.0.0.0:3389                         *:*                                     1300  [svchost.exe]                        TermService      
...

Adding lots of useful DateTime functions

Here are a couple of really useful methods you could add to the [DateTime] type.
 
Even if some are not exactly what you need it provides a framework for adding your own.
 
I’ve added them by putting the code below into a file in $PSHome named
DateTime.Types.ps1xml
Then in my $PSHome\Profile.ps1 I have the line
Update-TypeData (Join-Path $PSHome ‘DateTime.Types.ps1xml’)
Now anyone on the machine with a PowerShell Session has access to the extensions.
<?xml version="1.0" encoding="utf-8" ?>
<!--<# *******************************************************************

  This extension adds complex date manipulation functionality to
  [system.datetime] data types.  Specifically 2 Methods are added.  Each
  returns a [system.datetime] object with the requested value just like
  native methods such as AddYears or AddDays.

  AddBusinessDays([int]$Count)

    Returns the business day $Count days from $this date where 1 means
    next and -1 is prior.  Zero is treated opposite of TruncateTo('BD')
    i.e. it returns $this or the following Monday.  When the date falls
    on a weekend and $Count is not zero Monday or Friday are counted.
    That is <a-Sunday-date>.AddBusinessDays(1) results in Monday and
    <a-Sunday-date>.AddBusinessDays(-1) results in Friday.

  TruncateTo([string]$Part[,[int]$Offset])

    Truncates $this date to the date part named.

      Examples using:      $mydate = Get-Date '12/31/1999 17:47:12.345'
        $mydate.TruncateTo('Year')     Returns 01/01/1999 00:00:00.000
        $mydate.TruncateTo('QY')       Returns 10/01/1999 00:00:00.000
        $mydate.TruncateTo('BD')       Returns 12/31/1999 00:00:00.000
        $mydate.TruncateTo('Second')   Returns 12/31/1999 17:47:12.000
        $mydate.TruncateTo('Y',0)      Returns 01/01/1999 00:00:00.000
        $mydate.TruncateTo('FBDY',1)   Returns 01/03/2000 00:00:00.000
        $mydate.TruncateTo('18:00',1)  Returns 12/31/1999 18:00:00.000

    $Part is the part of the date to truncate to.  Either the abbreviated
          or long (FullySpelledOut) version of the part can be used.
               Y - Year
               M - Month
               D - Day
               H - Hour
              MI - Minute
               S - Second
          ******** Parts of a year
              HY - HalfYear       (Jan,Jul)
              QY - QuarterYear    (Jan,Apr,Jul,Oct)
            FBDY - FirstBusinessDayYear
          ******** Parts of a month
              HM - HalfMonth      (1st or 16th)
            FBDM - FirstBusinessDayMonth
          ******** Parts of a day
              HD - HalfDay        Midnight or Noon (12 hr period)
              TD - ThirdDay       Midnight, 8:am, 4:pm
              QD - QuarterDay     Midnight, 6:am, Noon, 6:pm
              BD - BusinessDay    BOD Today or most recent Fri on Sat or Sun
           hh:mm - SPECIFIC TIME  Some examples: "07:30", "14:00", "23:59"
          ******** Parts of an hour
              HH - HalfHour       :00 or :30 (30 minute period)
              TH - ThirdHour      :00, :20, :40 (20 minute periods)
              QH - QuarterHour    :00, :15, :30, :45 (15 minute periods)
              SH - SixthHour      :00, :10, :20, ... (10 minute periods)
          ******** Parts of a week
             Sun - Sunday         Truncates to most recent Sunday
             Mon - Monday
             Tue - Tuesday
             Wed - Wednesday
             Thu - Thursday
             Fri - Friday
             Sat - Saturday

    Note: Truncation without an offset will never return a future date.

    $Offset ..defaults to 0.  When non-zero, adds that number of $part
              periods to the result.  For example ('Y',2) first truncates
              to the current year then adds 2 years.  ('QH',1) used with
              17:37 yields 17:45 and ('QH',3) gives 18:15.
********************************************************************#> -->
<Types>
  <Type>
    <Name>System.DateTime</Name>
    <Members>
      <ScriptMethod>
        <Name>AddBusinessDays</Name>
        <Script>
    param ([int]$Count,[datetime]$From = $([datetime]::MinValue))
    # Use this if params don't seem to get passed 
    #[int]$Count = $args[0] 
    #[datetime]$From = [datetime]::MinValue 
    if ($From -eq [datetime]::MinValue) {$From = $This.AddDays(0)}
    while (!$Count -and 1..5 -notcontains $From.DayOfWeek.value__) {
        $From = $From.AddDays(1)}
    if ($Count -lt 0) {
      while ($Count++) {
        $From = $From.AddDays(-1)
        while (1..5 -notcontains $From.DayOfWeek.value__) {
          $From = $From.AddDays(-1)}
      }}
    else {
      while ($Count--) {
        $From = $From.AddDays(1)
        while (1..5 -notcontains $From.DayOfWeek.value__) {
          $From = $From.AddDays(1)}
      }}
    $From
        </Script>
      </ScriptMethod>
      <ScriptMethod>
        <Name>TruncateTo</Name>
        <Script>
    param ([string]$Part,[int]$Offset,[switch]$KeepTime)
    # Use this if params don't seem to get passed 
    #[string]$Part = $args[0] 
    #[int]$Offset = $args[1] 
    #[switch]$KeepTime = $args[2] 
    $Date = $this.Date
    $Time = $this.TimeOfDay
    $Year = $Date.AddDays((1-$this.DayOfYear))
    $DowB = [datetime]'1995/01/01'
    $Days = [math]::Truncate(($Date - $DowB).TotalDays)
    $Sign = [math]::Sign([math]::Sign($Days+1)-1)
    switch -regex ($Part) {
      # Parts of DateTime 
      '^(0?1|10|Y|Year)$' {
          $Year.AddYears($Offset)}
      '^(0?2|20|M|Month)$' {
          ($Date.AddDays((1-$Date.Day))).AddMonths($Offset)}
      '^(0?3|30|D|Day)$' {
          $Date.AddDays($Offset)}
      '^(0?4|40|H|Hour)$' {
          $Date.AddHours($Time.Hours+$Offset)}
      '^(0?5|Mi|Minute)$' {
          $Date.AddMinutes([math]::Truncate($Time.TotalMinutes)+$Offset)}
      '^(0?6|S|Second)$' {
          $Date.AddSeconds([math]::Truncate($Time.TotalSeconds)+$Offset)}
      # Parts of Year 
      '^(11|HY|HalfYear)$' {
          $Year.AddMonths(([math]::Truncate(($Date.Month -1)/6)+$Offset)*6)}
      '^(12|QY|QuarterYear)$' {
          $Year.AddMonths(([math]::Truncate(($Date.Month -1)/3)+$Offset)*3)}
      '^(19|FBDY|FirstBusinessDayYear)$' {
          $t1 = $t2 = $Year
          while (1..5 -notcontains $t1.DayOfWeek.value__)
            {$t1 = $t1.AddDays(1)}
          if ($Date.DayOfYear -lt $t1.DayOfYear) {
            $t2 = $t2.AddYears($Offset - 1)}
          else {
            $t2 = $t2.AddYears($Offset)}
          while (1..5 -notcontains $t2.DayOfWeek.value__)
            {$t2 = $t2.AddDays(1)}
          $t2}
      # Parts of Month 
      '^(21|HM|HalfMonth)$' {
          $t = $Date.AddMonths([math]::Truncate($Offset/2))
          if ($t.Day -ge 16) {
            switch ($Offset%2) {
              1 {$t.AddDays(1-$t.Day).AddMonths(1)}
             -1 {$t.AddDays(1-$t.Day)}
              0 {$t.AddDays(16-$t.Day)}}}
          else {
            switch ($Offset%2) {
              1 {$t.AddDays(16-$t.Day)}
             -1 {$t.AddDays(16-$t.Day).AddMonths(-1)}
              0 {$t.AddDays(1-$t.Day)}}}}
      '^(29|FBDM|FirstBusinessDayMonth)$' {
          $t1 = $t2 = $Date.AddDays(1-$Date.Day)
          while (1..5 -notcontains $t1.DayOfWeek.value__)
            {$t1 = $t1.AddDays(1)}
          if ($Date.Day -ge $t1.Day) {
            $t2 = $t2.AddMonths($Offset)}
          else {
            $t2 = $t2.AddMonths($Offset - 1)}
          while (1..5 -notcontains $t2.DayOfWeek.value__) {
            $t2 = $t2.AddDays(1)}
          $t2}
      # Parts of Day 
      '^(31|HD|HalfDay)$' {
          $Date.AddHours(([math]::Truncate($Time.Hours/12)+$Offset)*12)}
      '^(32|TD|ThirdDay)$' {
          $Date.AddHours(([math]::Truncate($Time.Hours/8)+$Offset)*8)}
      '^(33|QD|QuarterDay)$' {
          $Date.AddHours(([math]::Truncate($Time.Hours/6)+$Offset)*6)}
      '^(39|BD|BusinessDay)$' {
          $t = $Date
          while (1..5 -notcontains $t.DayOfWeek.value__)
            {$t = $t.AddDays(-1)}
          $this.AddBusinessDays($Offset,$t)}
      # Time of day 
      '^[0-9][0-9]?:[0-9][0-9]$' {
          $TimeOfDay = [TimeSpan]$_
          $(if ($this.TimeOfDay -gt $TimeOfDay) {
              $this.date + $TimeOfDay}
            else {$this.date.AddDays(-1) + $TimeOfDay}).AddDays($Offset)}
      # Parts of Hour 
      '^(41|HH|HalfHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/30)+$Offset)*30)}
      '^(42|TH|ThirdHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/20)+$Offset)*20)}
      '^(43|QH|QuarterHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/15)+$Offset)*15)}
      '^(44|SH|SixthHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/10)+$Offset)*10)}
      # Parts of Week 
      '^(71|Sun|Sunday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(0+$Sign))/7)+$Offset+$Sign)*7+0)}
      '^(72|Mon|Monday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(1+$Sign))/7)+$Offset+$Sign)*7+1)}
      '^(73|Tue|Tuesday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(2+$Sign))/7)+$Offset+$Sign)*7+2)}
      '^(74|Wed|Wednesday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(3+$Sign))/7)+$Offset+$Sign)*7+3)}
      '^(75|Thu|Thursday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(4+$Sign))/7)+$Offset+$Sign)*7+4)}
      '^(76|Fri|Friday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(5+$Sign))/7)+$Offset+$Sign)*7+5)}
      '^(77|Sat|Saturday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -(6+$Sign))/7)+$Offset+$Sign)*7+6)}
      default {
        # The numeric syntax is for testing and unsupported numbers return $null
        if ($_ -notmatch '^[0-9][0-9]?$') {
          throw ("Expecting one of`nY M D H Mi S HY QY FBDY HM FBDM " +
                 "HD TD QD hh:mm HH TH QH SH BD SUN MON TUE WED THU FRI SAT`n" +
                 "Year Month Day Hour Minute Second HalfYear " +
                 "QuarterYear FirstBusinessDayYear HalfMonth`n" +
                 "FirstBusinessDayMonth HalfDay ThirdDay QuarterDay " +
                 "BusinessDay HalfHour ThirdHour QuarterHour SixthHour`n" +
                 "Sunday Monday Tuesday Wednesday Thursday Friday Saturday")}}
    }
         </Script>
      </ScriptMethod>
    </Members>
  </Type>
</Types>

P.S. Looking at the code reveals that $part can be a number (not documented).
    This feature has 2 purposes.
    1) I have a scalar valued function in SQL Server with similar functionality.
       Because that is used in queries I wanted a more efficient arg in its case statement.
       These numbers match those...
    2) It makes for easier testing because I don't need to code all the valid type names.
       For example:

    
$e = [datetime]'2009/11/16 22:47:31.562'
1..80|%{'{0:D2}' -f $_}|
  %{if($e.TruncateTo($_))
    {   "$_ =" +
        " $($e.TruncateTo($_,-2).ToString('yyyy/MM/dd HH:mm:ss.f ddd')) |" +
        " $($e.TruncateTo($_,-1).ToString('yyyy/MM/dd HH:mm:ss.f ddd')) |" +
        " $($e.TruncateTo($_, 0).ToString('yyyy/MM/dd HH:mm:ss.f ddd')) |" +
        " $($e.TruncateTo($_, 1).ToString('yyyy/MM/dd HH:mm:ss.f ddd')) |" +
        " $($e.TruncateTo($_, 2).ToString('yyyy/MM/dd HH:mm:ss.f ddd'))"}}

Determining a file’s EOL character

A simple function to determine the type of EOL character used by a file.
When run against non-ascii encoding systems it can give false readings because the high order byte could be 10 or 13.
While I state this is possible, it should not be a problem for most languages.
function Get-EOL_Type ($FileName,[int]$MaxLen = 2000) {
    $Chars = Get-Content $FileName -encoding byte -totalcount $MaxLen
    if ($Chars -contains 13 -and $Chars -contains 10) {’CRLF’}
    elseif ($Chars -contains 13) {’CR’}
    elseif ($Chars -contains 10) {’LF’}
    else {’unk’}

}

Better than Splat @args

The Splat operator doesn’t support named args and and it cannot be used to pass args to a method.   These are serious drawbacks.
 
Completely solving the named args issue is currently not possible because PowerShell turns switches and argument names into strings and there is no way to tell if a given string actually had quotes or not in the original call statement (see bug report https://connect.microsoft.com/PowerShell/feedback/ViewFeedback.aspx?FeedbackID=368512 ).  If we ignore the possiblity of ambiguous strings (which should be rare anyway) we can come pretty close to a solution.  In this example, $args are being splatted as arguments to ‘Nested-Command’
 
$args|% -begin{$exp = ‘Nested-Command ‘
               $_ctr_ = 0} `
        -process{if($_ -is [string] -and $_ -match ‘^-[a-z0-9_]+$’) {
                   $exp += "$_ "}
                 else {set-variable (‘_tmp_’+ ++$_ctr_) $_
                       $exp += "`$$(‘_tmp_’ + $_ctr_) "} } `
        -end{Invoke-Expression $exp}
This next algorithm does exactly what the existing splat operator does.  That is, it only spreads the args out but doesn’t try to deal with named args.  But methods don’t need named args so it can pass args on to a method just fine.  In this example $args are being splatted as arguments to $Instance.Function
       $args|
         % -begin  {$exp = ‘$Instance.Function(‘
                    $_ctr_ = 0} `
           -process{set-variable (‘_tmp_’+ ++$_ctr_) $_
                    $exp += "`$$(‘_tmp_’ + $_ctr_),"} `
           -end    {$exp = $exp.Trim(‘,’) + ‘)’
                    Invoke-Expression $exp}

Edit-History

I use this function when working on remote systems where I don't have 
authority to create a function library locally on the remote system.
In such circumstances I can paste commands stored on my own system 
but it's a hasle to edit them and re-paste.  This allows me to easily
edit and re-execute multi-line commands in my remote session history.
######################################################################
#
#   Edit-History {<history number>|<cmd prefix>}
#
#   Creates a pop-up window to allow editing of the history item
#   having ID <history Num> or the most recent command in history
#   starting with <cmd prefix>.  Once edited the new command is added
#   to history and invoked.
#
#   NOTE: Because invocation happens within the scope of Edit-History
#         you may need to dot your invocation to yield proper results.
#         For example:
#   PS> Function myFcn ($parm) {'Do something with $parm'}
#   PS> Edit-History           # Change ' to " and close Textbox
#   Function myFcn ($parm) {"Do something with $parm"}
#   PS> myFcn 'this'
#   Do something with $parm
#
#   The function was modified but only within the scope of Edit-History
#   PS> . Edit-History fun     # cmd already has " type quotes just exit
#   Function myFcn ($parm) {"Do something with $parm"}
#   PS> myFcn 'this'
#   Do something with this
#
#   From what I understand, this problem (the dot needed) can only be
#   overcome by writing this function in a compiled language and
#   importing it into your session.  Interpreted code can't do it.
#   
######################################################################
function Edit-History ($Spec) {
  if ($Spec -is [int]) {$Spec = [int64]$Spec}
  if ($Spec -isnot [int64]) {
    $Entry = $_Cnt = (Get-History -Count 1).id
    while ($_Cnt -gt 0 -and
           ($Entry = $(Get-History -id $_Cnt -ErrorAction silentlycontinue)) -and
            $Entry -notlike ("$Spec" + '*')) {$_Cnt--}
    if ($Entry -and $_Cnt) {$Spec = $_Cnt}
  }
  if ($Spec -is [int64]) {
    $h = Get-History -id $Spec|
         Select-Object CommandLine,EndExecutionTime,ExecutionStatus,
                       Id,StartExecutionTime
      [void][System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
      $frmMain = new-object Windows.Forms.form
      $frmMain.Size = new-object System.Drawing.Size @(500,300)
      $frmMain.text = "Fix-History"
      $TextBox = new-object System.Windows.Forms.TextBox
      $frmMain.Controls.Add($TextBox)
      $TextBox.Dock = [System.Windows.Forms.DockStyle]::Fill
      $TextBox.MultiLine = $true
      $TextBox.AcceptsReturn = $true
      $TextBox.AcceptsTab = $true
      $TextBox.lines = $h.CommandLine.Split("`n")
      $FrmMain.Add_Shown({$FrmMain.Activate()})
      [void]$FrmMain.showdialog()
      $h.CommandLine = [string]::Join("`n",$TextBox.lines)
      if ($h.CommandLine) {
        Add-History $h
        Write-Host $h.CommandLine
        Invoke-Expression $h.CommandLine
      }
    }
  else {"No entry matches $([string]$Spec + '*')"}
}

Get-Signature

Note: Now that version 2 is out there is a similar, native function.

######################################################################
#
#   Get-Signature {<method>|<command>}
#
#   Returns the call signature of methods/functions.
#
#   Examples:
#     Get-Signature get-alias
#     Get-Signature ”.remove
#     Get-Signature [string]::join
#     Get-Signature more
#
######################################################################
function Get-Signature ($Cmd) {
  if ($Cmd -is [Management.Automation.PSMethod]) {
    $List = @($Cmd)}
  elseif ($Cmd -isnot [string]) {
    throw ("Get-Signature {<method>|<command>}`n" +
           "’$Cmd’ is not a method or command")}
  elseif (!($List = @(Get-Command $Cmd -ErrorAction SilentlyContinue)) -and
          $Cmd -like ‘`[*`]::*’ -and
          $Cmd -notlike ‘*)’) {
    $List = @(Invoke-Expression $Cmd  -erroraction SilentlyContinue)}
  if (!$List[0] ) {
    throw "Command ‘$Cmd’ not found"}
  foreach ($O in $List) {
    switch -regex ($O.GetType().Name) {
      ‘AliasInfo’ {
        Get-Signature ($O.Definition)}
      ‘ApplicationInfo’ {
        $O.Definition
        Invoke-Expression "$($O.Definition) /?"}
      ‘(Cmdlet|ExternalScript)Info’ {
        $O.Definition}          # not sure what to do with ExternalScript
      ‘F(unction|ilter)Info’{
        if ($O.Definition -match ‘^param *\(‘) {
          $t = [Management.Automation.PSParser]::tokenize($O.Definition,
                                                          [ref]$null)
          $c = 1;$i = 1
          while($c -and $i++ -lt $t.count) {
            switch ($t[$i].Type.ToString()) {
              GroupStart {$c++}
              GroupEnd   {$c–}}}
          $O.Definition.substring(0,$t[$i].start + 1)}
        else {$O.Name}}
      ‘PSMethod’ {
        foreach ($t in @($O.OverloadDefinitions)) {
          while (($b=$t.IndexOf(‘`1[[‘)) -ge 0) {
            $t=$t.remove($b,$t.IndexOf(‘]]’)-$b+2)}
            $t}}}}}

Display variables values at different scopes

I have an automated process for building the necessary regular expression using Emacs. It has a function called regexp-opt which builds an optimized regular expression to search for a list of words.The result is converted to a Powershell regex with a list of string replacements such as “\(” to “(” etc.

######################################################################
#
#                               MYVARS
#
#   Displays *ALL* Non-AUTOMATIC PS Variables in a scope range.
#   With the -del option it cleans up the variables in that range.
#   Requires Get-MaxScopeID posted by Kiron 20080917.
#
######################################################################
function MyVars([int]$minScope=0,
                [int]$maxScope=$(Get-MaxScopeID),
                [switch]$del) {
$ScopeLimit = (Get-MaxScopeID)
if ($minScope -gt $maxScope){throw "Myvars [<min>] [<max>]"}
"Maximum Scope range specification is 0..$($ScopeLimit - 1)"
if ($minScope -lt 0 -or $maxScope -lt 0){throw "Scope out of range."}
$maxScope++                     #These offsets are to account
$minScope++                     #for $this scope which goes away
if ($maxScope -gt $ScopeLImit){$maxScope = $ScopeLimit}
if ($minScope -gt $maxScope)  {$minScope = $maxScope}
$minScope..$maxScope|%{Get-Variable -scope $_|Select-Object Name,Value|
                       Add-Member noteproperty 'Scope' ($_ - 1) -passthru}|
 ?{$_.name -notmatch (
   '^(Co(?:mmandLineParameters|n(?:(?:firmPreferenc|soleFileNam)e))|DebugPref' +
   'erence|E(?:rror(?:ActionPreference|View)?|xecutionContext)|FormatEnumerat' +
   'ionLimit|H(?:OME|ost)|L(?:ASTEXITCODE|oad_Library_Paths)|M(?:aximum(?:(?:' +
   'Alias|Drive|Error|Function|History|Variable)Count)|yInvocation)|NestedPro' +
   'mptLevel|OutputEncoding|P(?:ID|ROFILE|S(?:BoundParameters|C(?:mdlet|ultur' +
   'e)|EmailServer|HOME|MaximumReceived(?:(?:DataSizePerCommand|ObjectSize)MB' +
   ')|Session(?:(?:Applic|Configur)ationName)|TypePath|(?:UICultur|VersionTab' +
   'l)e)|WD|rogressPreference)|ReportErrorShow(?:ExceptionClass|InnerExceptio' +
   'n|S(?:(?:our|tackTra)ce))|S(?:hellId|tackTrace)|VerbosePreference|W(?:SMa' +
   'nMaxRedirectionCount|(?:arning|hatIf)Preference)|args|false|input|null|pa' +
   'ttern|true|[$?_^])$'
   )}|
 %{Add-Member -in $_ noteproperty 'Type'  $(if ($_.Value){
              $_.Value.PSTypeNames[0]}else{'Null'}) -passthru}|
 Sort-Object -property scope,name|
 %{if ($del) {Remove-Variable $_.Name -scope ($_.Scope + 1)
              "Deleted $($_.name)"}else{$_}}|
 Format-Table Scope,Name,Type,Value -a
}
######################################################################
#
#                             Get-MaxScopeID
#
#   Returns the numeric -scope value currently equivalent to 'global'
#   Posted by iron 20080917 on microsoft.public.windows.powershell NG
#
######################################################################
function Get-MaxScopeID () {
 # -2 below accounts for function and foreach scopes which go away
 trap {return ([int]$id - 2)}
 foreach ($id in 0..100) {
  gv -s $id >$null
 }
}

Generic way to see if an object is castable to another type

This function provides a generic way to tell if a given object can safely be cast to another type.
It returns a boolean value (True False) so it can also be used as a filter in a pipeline.
filter Global:CanBe([string]$Type,$InputObject) {
  begin {if ($InputObject){$InputObject|CanBe $Type}}
  process { if (!$InputObject) {
    Invoke-Expression "&{trap {`$false;continue};[boolean]([$Type]`$_)}"}
  }
}
Here are some test cases you can try.
CanBe int 'rick'
CanBe int '12,345'
'abc',123,'124',@(),{throw},@('hu',1)|CanBe int
'abc',123,'124',@(),{throw},@('hu',1)|CanBe string
'abc',123,'124',@(),{throw},@('hu',1)|CanBe scriptblock
'abc',123,'124',@(),{throw},@('hu',1)|CanBe object[]
'abc',123,'124',@(),{throw},@('hu',1)|?{CanBe scriptblock $_}
'abc',123,'124',@(),{throw},@('hu',1)|?{CanBe string $_}

PowerShell Class Definition Library

If you read Windows PowerShell In Action you might have thought that the section on ‘Extending the PowerShell language’ was interesting.  Particularly the ‘Adding a CustomClass Keyword’ section.  I basically tried to go all out to provide a full blown custom-class solution.  There are limits to what is possible but this emulation is pretty good.  I use it every day so it should be stable.  I make updates occasionally.  Check back regularly.  You’re welcome to report bugs or suggest improvements.
 
This code was updated 10/28/08.  It is now radically different from the original which was strongly based on Bruce Payette’s version.  This version supports inheritence, serialization and (more importantly) resurection of objects to a working state after being de-serialized.
 
I put the doc in a here-string because a seperate file begs to get out of sync.

#REQUIRES -VERSION 2
$ErrorActionPreference = 'Stop'
[void]@'
                        Class Definition Library

Lets you Define, Get, Instantiate Rebuild and Remove - classes and instances.

EXAMPLE:

  Add-Class Example {
    Constructor {
      param ([string]$InstanceName)
      Property       Instance_call_count  0
      Property       MyName               $InstanceName
    }
    StaticProperty   Class_call_count     0
    Method ToString {
      "`t$($this.type) called $([string]++$this.Class_call_count) times`n" +
      "`t$($this.MyName) Called $([string]++$this.Instance_call_count) times"
    }
  }
  $a = New-ClassInstance Example "Instance A"
  $b = New-ClassInstance Example "Instance B"

  $a.ToString()
        Example called 1 times
        Instance A Called 1 times.
  $b.ToString()
        Example called 2 times
        Instance B Called 1 times.
  $a.ToString()
        Example called 3 times
        Instance A Called 2 times.
  $a,$b|ft -a

  Type    Class_call_count Instance_call_count MyName
  ----    ---------------- ------------------- ------
  Example                3                   2 Instance A
  Example                3                   1 Instance B

DOCUMENTATION:

Add-Class <name>[,<parent>] {<definition>}       # Defines the Class <name>.
                                # <definition> is a Script Block where the class
                                # Constructor, Methods and StaticProperties are
                                # defined.  When <parent> is included your new
                                # class first inherits all StaticProperties and
                                # Methods of the parent class.  IOW all the
                                # StaticProperties and Methods of that class are
                                # implicitly defined.  Instance properties are
                                # defined in your Constructor code and they too
                                # are inherited.

Remove-Class <name>             # Deletes the definition for class <name>.  This
                                # does not delete existing instances.  It only
                                # renders them effectively useless because it
                                # deletes their methods and static properties
                                # out from under them.  The objects still exist
                                # and their properties are still accessible.

New-ClassInstance <name> [<args>] # Creates an instance of a class.  <args> are
                                # arguments to the class constructor.

The following are the special functions available within <definition> code to
declare your class constructor and any methods or static properties.  They are
all public.  Instance properties are covered later.  The order in which you
declare class elements is not important unless you declare the same element
multiple times.  There is no safeguard against this and the last definition
overrides prior definitions.

Constructor <scriptblock>       # <scriptblock> contains your class constructor
                                # code.  Here you interpret constructor args and
                                # declare/initialize instance properties.  See
                                # the Property keyword below.  Note that when
                                # inheriting from another class you must create
                                # an alias for the parent constructor and call
                                # it yourself if you both overload it and want
                                # it called.  See AliasParentMethod below.  The
                                # only difference between a constructor and any
                                # other method is that you can declare class
                                # properties in them.  In fact you can use the
                                # syntax: Method Constructor {scriptblock}
                                # instead if you want.  A constructor is not
                                # required however unless you define or inherit
                                # a constructor, your class will have no
                                # instance properties.

Method <name> <scriptblock>     # <scriptblock> defines a method of the class.
                                # Accessed via $obj.<name>([<args>]) where $obj
                                # is an instance of the class.  Methods access
                                # other properties and Methods via $this.<name>

StaticProperty <name> <value>   # Defines a variable whose value is accessible
                                # to all members of the class via the syntax:
                                # $this.<name>.  Although regular properties are
                                # accessed by the same syntax, any change to a
                                # StaticProperty is seen by all instances.

AliasParentMethod <parent method name> <alias> # Use this to give a parent
                                # method that you override a name by which the
                                # parent Method can still be invoked.  Otherwise
                                # $this.<method> always invokes your function
                                # and the parent function is inaccessible.

AliasParentStaticProperty <parent static property name> <alias> # Use this to
                                # give your methods access to a parent static
                                # property that you override.  While parent
                                # methods invoked from your class will only see
                                # your instance of a static property you
                                # overrode, there may still be interactions with
                                # objects based on your parent class but not
                                # based on your class.  This provides a
                                # mechanism for managing those situations.

NOTE: Every class has 2 built-in StaticProperties: "Type" and "ParentType".
"Type" is a [string] containing the name of the class.  ParentType is a
[string] containing $null or the parent class upon which this class is built.
This lets you check the class name of an object like this.

if ($var -is [PSObject] -and $var.Type -eq "Custom") ...
if ($var -is [PSObject] -and $var.ParentType -eq "A_Mom") ...

The names are also added to PSObject.TypeNames so these work too.

if ($var.PSObject.TypeNames -contains "Custom")
if ($var.PSObject.TypeNames -contains "A_Mom")

Within Constructor <ScriptBlock> code you can do whatever you like.  Typically
you interpret arguments and initialize properties.  The only special function
available in a constructor ScriptBlock is the Property function.

Property <name> [<value>]       # Each property declared in a constructor is
                                # available to class Methods via $this.<name>.
                                # Where <value> is the initial value.  Each
                                # instance of the class references a private
                                # copy of the value and so each instance is
                                # unique by virtue of these properties.

The following function allows access to class Static properties and methods.
Static methods are simply methods that do not reference instance properties.

Get-Class [<name>]              # Returns an object that represents the
                                # definition a class or a list of all defined
                                # classes.

A list of all defined classes is available like this:

get-class|%{$_.type}

External access to a static property or method of a class is made like this:

(Get-Class <class name>).<method name>

WARNING: From within class methods you should not use this syntax to access your
own class methods and properties.  You should always use $this.<name>.  Access
other than through $this circumvents any overriding done by a derived classes
which is almost always the wrong thing to do.
'@

function global:Add-Class ([string[]]    $type,
                           [scriptblock] $script,
                           [switch]      $Force) {
     #
     # Validate arguments.
     #
     if (! $type -or ! $script) {
       throw "Syntax: Add-Class '<type>'[,'<parent>'] {<script>} -- '$type'"}
     if ($type.Count -gt 2) {
       throw "You can only inherit from 1 class - '$type'"}
     $Parent = $type[1]
     [string]$type = $type[0]
     # Re-definition is (usually) not legal.
     # When using -Force an existing definition is changed rather than being
     # replaced so that existing class instance objects are not orphaned from
     # their underlying code.  This feature is only a testing aid.  If you add
     # new properties or methods, existing instances will not know about them.
     # Rebuild-Instance can be used in this case.
     if (($Class = Get-Class $type) -and !$Force) {
       throw "Error: '$type' already defined.  Use Remove-Class to re-define."}
     # Parent Class must exist too
     if ($Parent -and !(Get-Class $Parent)) {
       throw "Error: Can't inherit from '$Parent'.  Class not defined."}
     if ((Get-Class -i).InvalidName($type)) {
       throw "Illegal class name - '$type'"}
     #
     # DEFINE THE FUNCTIONS THAT ALLOW CLASS FEATURES TO BE DECLARED
     #
     function Property {Throw ("Define Properties in Constructor scripts.`n" +
                               "Use StaticProperty for static properties.")
     }
     function AliasParentMethod ([string]$name,[string]$alias)  {
         if (!$PClass) {
           throw "Can't use AliasParentMethod when not inheriting"}
         if (! $name -or ! $alias) {
           throw "Syntax: AliasParentMethod '<name>' '<alias>' -- $args"}
         if ((Get-Class -i).InvalidName($alias)) {
           throw "Illegal Method Alias name '$alias'"}
         # The method to be renamed must exist on the parent Class object.
         $Method = $PClass.PSObject.Members.Item($name)
         if (!$Method -or $Method.MemberType -ne 'ScriptMethod') {
           throw $("Syntax: AliasParentMethod '<name>' '<alias>'`n" +
                   "'$name' is not a Method of '$($PClass.Type)'")}
         # Overwriting the existing code rather than adding a new object keeps
         # existing pointers to the data from becoming invalid.
         if ($Class.PSObject.Methods -contains $alias -and
             $Class.$alias.MemberType -eq 'ScriptMethod') {
           $Class.$alias.script = $Method.Script}
         else {
           $Class|Add-Member ScriptMethod $alias $Method.Script -Force}
     }
     function AliasParentStaticProperty ([string]$name,[string]$alias) {
         if (!$PClass) {
           throw "Can't use AliasParentMethod when not inheriting"}
         if (! $name -or ! $alias) {
           throw "Syntax: AliasParentStaticProperty '<name>' '<alias>' -- $args"}
         if ((Get-Class -i).InvalidName($alias)) {
           throw "Illegal Method Alias name '$alias'"}
         # The property to be renamed must exist on the parent Class object.
         $Prop = $PClass.PSObject.Members.Item($name)
         if (!$Prop -or ($Prop.MemberType -ne 'NoteProperty' -and
                         $Prop.MemberType -ne 'ScriptProperty')) {
            throw $("Syntax: AliasParentStaticProperty '<name>' '<alias>'`n" +
                     "'$name' is not a Static Property of '$($PClass.Type)'")}
         # Create accessors that point to the parent static value.
         $T1 = "(`$global:__Defined_Classes__['$Parent']).$name"
         $T2 = $t1  + ' = $args[0]'
         $T1 = $ExecutionContext.InvokeCommand.NewScriptBlock($T1)
         if ('Type','ParentType' -contains $name) {
             $T2 = $null} # Setters for these types are not allowed
         else {
           $T2 = $ExecutionContext.InvokeCommand.NewScriptBlock($T2)}
         # Overwriting the existing code rather than adding a new object keeps
         # existing pointers to the data from becoming invalid.
         $Property = $Class.PSObject.Properties.Item($alias)
         if ($Property -and $Property.MemberType -eq 'ScriptProperty') {
           $Property.GetterScript = $T1
           $Property.SetterScript = $T2}
         else {
           $Class|Add-Member ScriptProperty $alias -Force `
                      -Value $T1 -SecondValue $T2}
     }
     function Constructor ([scriptblock] $script) {
         # Nobody should point to this so don't worry about saving pointers.
         # The very special name (contains a space) tries to insure we don't
         # step on user functions.
         $Class|Add-Member ScriptMethod 'Constructor' $script -Force
     }
     function Method ([string]$name, [scriptblock] $script) {
         if (! $name)
            {throw "Syntax: method '<name>' {<script>} -- <name> missing"}
         if ((Get-Class -i).InvalidName($name))
            {throw "Illegal Method name '$name'"}
         # Overwriting the existing code rather than adding a new object keeps
         # existing pointers to the data from becoming invalid.
         if ($Class.PSObject.Methods -contains $name -and
             $Class.$name.MemberType -eq 'ScriptMethod') {
           $Class.$alias.script = $Method.Script}
         else {
           $Class|Add-Member ScriptMethod $name $script -Force}
     }
     function StaticProperty ([string]$name, $value)
     {   if (! $name)
            {throw "Syntax: StaticProperty '<name>' <value> -- <name> missing"}
         if ((Get-Class -i).InvalidName($name))
            {throw "Illegal StaticProperty name '$name'"}
        # You can't point to a NoteProperty so replacing one is OK'
        $Class|Add-Member NoteProperty $name $value -Force
     }
     #
     #          Build the complete definition used to build an instance
     #
     $PClass = $null
     if (!$Class) {$Class  = New-Object PSObject} # might be a re-definition
     if (!($Class.PSObject.TypeNames -eq $Type)) {
       $Class.PSObject.TypeNames.Insert(0,$Type)}
     $Class|Add-Member NoteProperty 'ParentType' $Parent -Force
     $Class|Add-Member ScriptMethod 'Class` Def' $script -Force
     $Class|Add-Member NoteProperty 'Type'       $type   -Force 
     # Make a stack of the classes that this one is built upon.
     $Stack = new-object System.Collections.Stack
     $TClass = $Class
     while ($TClass.ParentType) {
       $Stack.Push($TClass)
       if (!($Class.PSObject.TypeNames -eq $TClass.ParentType)) {
         $Class.PSObject.TypeNames.Insert(0,$TClass.ParentType)}
       if (!($TClass = (Get-Class $TClass.ParentType))) {
         throw "A parent class definition is missing '$(($Stack.Pop()).ParentType)'"}
     }
     # With the LIFO stack of definitions, build the complete definition.
     while ($TClass) {
       # Execution of the class definition script declares the methods
       # and static properties etc. on $Class because the functions like
       # Constructor and Method (defined above) are hard-coded to do so.
       [void]$TClass.PSObject.Methods['Class` Def'].Script.Invoke()
       # Creating an alias for a parent class method or property requires
       # access to the parent class.  The alias functions (defined above)
       # are coded to look for that data in $PClass.
       $PClass = $TClass
       # Get the next class or null if the hierarchy is ended.
       $TClass = $(if ($stack.count) {$Stack.Pop()})
     }
     # Add the new definition or replace an existing one
     $global:__Defined_Classes__[$type] = $Class
}

function global:Get-Class ([string] $name, [switch]$internal) {
     if ($name -match ' ') {throw "$name is an illegal class name"}
     #
     # Create (if needed) a location for class defs and private fcns.
     #
     if (!(test-path variable:global:__Defined_Classes__)) {
       $global:__Defined_Classes__ = @{}
       $T = New-Object PSObject
       $T|Add-Member ScriptMethod 'InvalidName' {('Type', 'ParentType',
                     'PSTypeNames', 'PSAdapted', 'PSBase', 'PSExtended',
                     'PSObject' -contains $args[0])}
       $global:__Defined_Classes__['Lib Internal'] = $T
     }
     #
     # $Internal returns an object with private library values.
     #
     if ($internal) {return $global:__Defined_Classes__['Lib Internal']}
     #
     # Either get all Class entries or just the one(s) requested.
     #
     if (! $name){
       $global:__Defined_Classes__[[string[]]($global:__Defined_Classes__.keys|
               ?{$_ -notmatch ' '})]}
     elseif ($global:__Defined_Classes__) {
       $global:__Defined_Classes__[$name]}
     else {$null}
}

function global:New-ClassInstance ([string] $type,
                                   [switch]$__DontInitialize__) {
     #
     #  CLASS WRITERS USE THIS FUNCTION TO DECLARE INSTANCE PROPERTIES
     #
     function Property ([string]$name, $value)
     {   if (! $name)
            {throw "Syntax: property '<name>' <value>  -- <name> missing"}
         if ('Type','ParentType','PSTypeNames','PSAdapted','PSBase',
               'PSExtended','PSObject' -contains $name -or
             $name -match '[^0-9A-Za-z_^]')
            {throw "Illegal Property name '$name'"}
         if ($Instance.PSObject.members.item($name))
            {throw "Instance already has a Method or Property named '$name'"}
         $Instance|Add-Member NoteProperty $name $value
     }
     #
     #                      CREATE THE NEW INSTANCE
     #
     $Class = Get-Class $type
     if (!$Class) {throw "Class '$type' doesn't exist."}
     $Instance = New-Object PSObject 
     #
     #                        ADD THE CLASS NAMES
     #
     $Class.PSObject.TypeNames|
       %{if (!($Instance.PSObject.TypeNames -eq $Type)) {
           $Instance.PSObject.TypeNames.Insert(0,$Type)}} 
     #
     #                       ADD DECLARED METHODS
     #
     $Class.PSObject.methods|
         ?{$_.MemberType -eq 'ScriptMethod' -and $_.name -notmatch ' '}|
         %{$Instance.PSObject.members.add($_)}
     #
     #                       ADD STATIC PROPERTIES
     #
     $Class.PSObject.properties|
       ?{$_.MemberType -eq 'NoteProperty'}|
       %{$T1 = "(`$global:__Defined_Classes__['$type']).$($_.Name)"
         $T2 = $T1  + ' = $args[0]'
         $T1 = $ExecutionContext.InvokeCommand.NewScriptBlock($T1)
         if ('Type','ParentType' -contains $_.Name) {
           $T2 = $null} # Setters for these types are not allowed
         else {
           $T2 = $ExecutionContext.InvokeCommand.NewScriptBlock($T2)}
         $Instance|Add-Member ScriptProperty $_.name -Force `
                      -Value $T1 -SecondValue $T2}
     #
     #                 ADD REDIRECTED STATIC PROPERTIES
     #
     $Class.PSObject.properties|
         ?{$_.MemberType -eq 'ScriptProperty'}|
         %{$Instance.PSObject.members.add($_)}
     #
     #                 CALL THE CONSTRUCTOR (if needed)
     #
     if ($Instance.PSObject.Methods['Constructor'] -and !$__DontInitialize__) {
       # This works around the lack of a (working) splat operator
       $args|
         % -begin  {$exp = '$Instance.Constructor('
                    $_ctr_ = 0} `
           -process{set-variable ('_tmp_'+ ++$_ctr_) $_
                    $exp += "`$$('_tmp_' + $_ctr_),"} `
           -end    {$exp = $exp.Trim(',') + ')'
                    Invoke-Expression $exp}|
       Out-Null
     } 
     # Done.  Return it.
     $instance
}

function global:Remove-Class ([string] $type = $(throw 'A valid type is required'),
                              [switch] $Force) {
     if (Get-Class $type) {
       $global:__Defined_Classes__.remove($type)}
     elseif (!$Force) {
       throw "Type '$type' is not defined"}
}

# Rebuild-Instance recursively replaces static properties and methods on the
# object passed with the methods and properties currently defined for that
# object class.  Its purpose is mainly to resurrect serialized objects but it
# can be useful in testing if you change a class definition and want to update
# some existing objects to use the new definition.

# IMPORTANT: When you serialize an instance, the fact that a property was
# Static is lost.  To account for this, when rebuilding a static member if the
# instance being rebuilt has a non-null value, the static value is replaced by
# it.  If the instance only has a pointer to a static value then the pointer is
# refreshed.  The idea here is that all instances should have the same value
# for static properties.  If you are rebuilding because the instance is being
# resurrected from an XML file, the static value would have been converted to
# an instance value and thus the value would have been preserved.  So this
# tries to convert the value in $obj back to being static.

function global:Rebuild-Instance ([PSObject]$obj,
                                  [switch]$NoStaticReplace) {
    # $obj must have a type property
    if (!($obj -and $obj.Type)) {
      throw "Syntax: Rebuild-Class <class-instance> [-NoStaticReplace]"}
    # See if the type is defined and get
    $Type = $obj.type
    if (!($TypeDef = Get-Class $Type)) {
      throw "Can't rebuild object, type '$Type' not defined"}
    # Build a new object just to get all the current class elements.
    $New = New-ClassInstance $Type -__DontInitialize__
    # Replace all the old methods
    $New.PSObject.Methods|
      ?{$_.MemberType -eq 'ScriptMethod'}|
      %{$obj|Add-Member ScriptMethod $_.Name $_.Script -Force}
    # Replace accessors to static properties.  This means tossing the non-static
    # version if it exists and updating the static value to the non-static one.
    $New.PSObject.Properties|?{$_.MemberType -eq 'ScriptProperty'}|
      %{# This replaces the static value with the instance value if needed.
        if ($obj.$($_.name) -ne $null -and
            $obj.$($_.name).MemberType -eq 'NoteProperty' -and
            !$NoStaticReplace) {
          $TypeDef.$($_.name) = $obj.$($_.name)}
        # Restore/Refresh the Getter/Setter for this property
        $obj|Add-Member ScriptProperty $_.Name -Force `
                   -Value $_.GetterScript -SecondValue $_.SetterScript}
    # Recurse into nested objects and rebuild them too
    $obj.PSObject.properties|
      ?{$_.MemberType -eq 'NoteProperty' -and
        $_.Value -is [PSObject] -and
        $_.Value.Type}|
      %{Rebuild-Instance $_.value -NoStaticReplace:$NoStaticReplace}
    # recurse into nested lists to rebuild any of 'our' objects
    $obj.PSObject.properties|
      ?{$_.MemberType -eq 'NoteProperty' -and
        $_.Value -is [Array]}|
      %{$_.Value|
        ?{$_ -and $_ -is [PSObject] -and $_.Type}|
        %{Rebuild-Instance $_}}
}

# LocalWords:  MemberType GetType ScriptMethod StaticProperty ScriptBlock args
# LocalWords:  PSObject NoteProperty ClassInstance ScriptProperty PSAdapted eq
# LocalWords:  ParentType AliasParentMethod PSTypeNames PSBase TypeNames param
# LocalWords:  PSExtended PowerShell's PSCustomObject InvalidName defs fcns
# LocalWords:  StaticProperties AliasParentStaticProperty InstanceName ToString
# LocalWords:  NoStaticReplace MyName

# SIG # Begin signature block
# MIIEKgYJKoZIhvcNAQcCoIIEGzCCBBcCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR
# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUnTj5AKjmAb60nI92xbA7Q9aw
# 4xagggI0MIICMDCCAZ2gAwIBAgIQlTIOA6/HvqJAFvdEzio63DAJBgUrDgMCHQUA
# MCwxKjAoBgNVBAMTIVBvd2VyU2hlbGwgTG9jYWwgQ2VydGlmaWNhdGUgUm9vdDAe
# Fw0wODAzMDcxNDMyMTBaFw0zOTEyMzEyMzU5NTlaMBExDzANBgNVBAMTBkEwSjY1
# MjCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAv0yAvkSPkcGd8YBnWbxerzKr
# kQJWUfHXDtQlHefTmRXcjOZZ0XAoZINA4XVJFdJvNC/Qp8l4Uv14Ea9MYNByUELH
# REAU4P3lCb3i3wPTxbKQVv/pWu1IZjReZJljeL8gmBa0QAeZ9rLagoxHziChDZ1S
# MtFcbEwXYy0KY2q4QKsCAwEAAaN2MHQwEwYDVR0lBAwwCgYIKwYBBQUHAwMwXQYD
# VR0BBFYwVIAQL3ZRv2pYKftzC3bM4Z22lqEuMCwxKjAoBgNVBAMTIVBvd2VyU2hl
# bGwgTG9jYWwgQ2VydGlmaWNhdGUgUm9vdIIQ41ebY7VoLJtFHAsM0UMAnTAJBgUr
# DgMCHQUAA4GBALYT6aYBK3nx6vpOlTRXZ4cPNv3C0Q5BGZvdJ0VVaNPEScQDt0dr
# 4Qr9izYgPAaarpMvr+Nk9ugpRtTDTQRzY3y8MN9Oa/WPyEVFR3ZSh/GjFkBEbVHC
# LvneK5dNVOe0NZLcPa5VJmFDUwvj6WCJlVW28G/pMFU2HyVpftoBqC9IMYIBYDCC
# AVwCAQEwQDAsMSowKAYDVQQDEyFQb3dlclNoZWxsIExvY2FsIENlcnRpZmljYXRl
# IFJvb3QCEJUyDgOvx76iQBb3RM4qOtwwCQYFKw4DAhoFAKB4MBgGCisGAQQBgjcC
# AQwxCjAIoAKAAKECgAAwGQYJKoZIhvcNAQkDMQwGCisGAQQBgjcCAQQwHAYKKwYB
# BAGCNwIBCzEOMAwGCisGAQQBgjcCARUwIwYJKoZIhvcNAQkEMRYEFEMAVA9nxLrY
# FWSin4R9tcfOibx0MA0GCSqGSIb3DQEBAQUABIGAU8ZlvWgRxUkLdVS4szkFpiUa
# mtXF0UvIpMcKjmc6ayvV980qMs91MQoz1s9l2AETlVaPJyTor52E/WisyJaUqYsr
# djON/dUd/Dh2Gjulmay48zuEyVY42JzuBkqOHsZT9MBLmMhpSDELqylEtgt5yNlL
# 2JvfS+PCswFZxFMI0Iw=
# SIG # End signature block