PoshCode Archive  Artifact Content

Artifact 278b711c08be46cb4119f0c5d6ea65944c23ba1e5b9387444d63482c6eef9194:

  • File TSQL-Auto-Programming.ps1 — part of check-in [2b509ba266] at 2018-06-10 14:15:23 on branch trunk — This function is simple example of using SMO to iterate through some database objects, tables (user: Brandon Warner size: 22625)

# encoding: ascii
# api: powershell
# title: TSQL Auto-Programming
# description: This function is simple example of using SMO to iterate through some database objects, tables 
# version: 2016.06.23
# type: script
# author: Brandon Warner
# license: CC0
# function: Get-WordWrappedText
# x-poshcode-id: 6422
# x-archived: 2016-07-01T08:44:27
# x-published: 2016-06-28T15:43:00
#
# and table rows, in this case, in order to generate a T-SQL script. In this example, I have 
# created a script that creates a view for each table that selects the top 100 rows of table. 
# Though I doubt this would be very useful, this is simply to exemplify the technique of 
# looping through the SMO API to generate code. This can be extremely powerful in generating 
# a variety of scripts based on property values available in this API.
#
<#+----------------------------------------------------------------------------------------------+#
  | Load Assemblies                                                                              |
 #+----------------------------------------------------------------------------------------------+#>
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')          | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended')  | Out-Null

<#
.SYNOPSIS
Takes a text string and word-wraps it to a specific length. 

.DESCRIPTION
This function takes a string of text and word-wraps it to a specific target line width. In addition 
to word-wrapping the applies optionally prefix and suffix to each line, and also an optional 
indent based on tab with and indent level.

.NOTES
+--------------------------------------------------------------------------------------------------+
| ORIGIN STORY                                                                                     |
+--------------------------------------------------------------------------------------------------+
|   DATE        : 2016-06-23
|   AUTHOR      : Brandon Warner
|   DESCRIPTION : Initial Draft
+--------------------------------------------------------------------------------------------------+
|   DATE        : 2016-06-27
|   AUTHOR      : Brandon Warner
|   DESCRIPTION : Fixed bug with line wrapping for lines less than the wrap-width
                  Also removed line endings from the last line of the returned wrapped text
+--------------------------------------------------------------------------------------------------+

.PARAMETER Text
The source text that we want to word-wrap.

.PARAMETER WrapWidth
The target width of one line in the wrapped text.

.PARAMETER Prefix
A text prefix that should be appended to the beginning of each line in resulting wrapped text
(useful for box drawing)

.PARAMETER Suffix
A text suffix that should be appended to the end of each line in resulting wrapped text
(useful for box drawing)

.PARAMETER TabWidth
Defines the character length of each indent level, if a non-zero indent level is supplied.

.PARAMETER IndentLevel
Indicates the number of levels to indent the resulting wrapped text.

.EXAMPLE

$ThisText  = 'Hello this is a really, really, really, really long sentence that I want to be word-wrapped'
$ThisText += ' so that it displays nicely in a document (either printed or on screen) so that I avoid having to scroll'
$ThisText += ' back and forth, back and forth, wasting both time and effort, that will not have to be spent,'
$ThisText += ' when the text is properly formatted.'

Get-WordWrappedText `
  -Text $ThisText `
  -WrapWidth 50 `
  -Prefix '>' `
  -IndentLevel 2 | oh

#>
function Get-WordWrappedText 
  {
    [CmdletBinding()]
    Param
      (
          [Parameter(Mandatory = $true)]
          [string]$Text  

        , [Parameter(Mandatory = $false)]
          [ValidateRange(25,150)]
          [Int]$WrapWidth = 100

        , [Parameter(Mandatory = $false)]
          [ValidateLength(1,25)]  
          [String]$Prefix = ''
  
        , [Parameter(Mandatory = $false)]
          [ValidateLength(1,25)]
          [String]$Suffix = ''  
  
        , [Parameter(Mandatory = $false)]
          [ValidateRange(1,8)]
          [Int]$TabWidth = 2
  
        , [Parameter(Mandatory = $false)]
          [ValidateRange(0,75)]
          [Int]$IndentLevel = 0    
      )
  
    if(($WrapWidth - "$Prefix$Suffix".Length) -lt 10)
      { 
        Write-Error `
          -Message "The wrap width (minus prefix and suffix length) is too short. There should be at least 10 characters width of text space left over after the prefix and suffix." 
      }
  
    [String]$Result       = ''
    [String]$ThisLine     = ''
    [String]$IndentSpace  = ''.PadLeft($TabWidth*$IndentLevel)
    [Int]$WorkingWidth    = $WrapWidth - $Prefix.Length - $Suffix.Length - 2 #Allow for prefix suffix and two spaces
   
    <#+--------------------------------------------------------------------+#\
      | See if the whole thing fits on one line                            |
    \#+--------------------------------------------------------------------+#>
    [Int]$LineWidth     = "$Prefix $Text $Suffix".Length
    
    if($LineWidth -le $WrapWidth)
      {
        return "$IndentSpace$Prefix $Text$("$Suffix".PadLeft($WrapWidth - $Text.Length - $Prefix.Length))"
      }
  
    <#+--------------------------------------------------------------------+#\
      | Proceed with word-wrapping the text                                |
    \#+--------------------------------------------------------------------+#>  
    else
      {
        [Bool]$Done         = $false
        [Int]$Position      = 0
        [Int]$PositionPrev  = 0

        while(!$Done)
          {
            $PositionPrev = $Position 
            $Position += $WorkingWidth
   
            if($Position -lt $Text.Length)
              {        
                <#+-------------------------------------------+#\
                  | Inch back till you hit a space            |
                \#+-------------------------------------------+#>  
                while(!($Text.Substring($Position,1) -match '\s') -and ($Position -gt $PositionPrev))
                  { 
                    $Position-- 
                  }
              }
            else
              {
                <#+--------------------------------------------------------------------+#\
                  | If we over-shot, inch back to the end of the text                  |
                \#+--------------------------------------------------------------------+#>
                while($Position -ge $Text.Length)
                  { 
                    $Position-- 
                  }              
                $Done = $true
              }
      
            <#+------------------------------------------------------------------------------+#\
              | If there entire line width was unbroken by white space, break the word       |
            \#+------------------------------------------------------------------------------+#>
            if($Position -eq $PositionPrev)
              {
                $Position += $WorkingWidth 
              }
      
            $LineWidth = $Position - $PositionPrev 

            $Result += "$IndentSpace$Prefix $($Text.Substring($PositionPrev,$LineWidth))$(" $Suffix".PadLeft($WrapWidth-$LineWidth-$Prefix.Length))"
            if(!$Done)
              {
                $Result += "`r`n"
              }
      
            <#+--------------------------------------------------------------------+#\
              | Inch past white space to the next word                             |
            \#+--------------------------------------------------------------------+#>      
            while(($Text.Substring($Position,1) -match '\s') -and ($Position -lt $Text.Length))
              { 
                $Position++ 
              }
          }   
      }
    return $Result
  }

<#
.SYNOPSIS
Returns comment block of word-wrapped text inside a character based box drawing.

.DESCRIPTION
Given a string of body text which you want to appear in a comment block surrounded in box
drawing characters, produces said comment block, word-wrapped to a given width and indented
to a given level as per the indent level and tab width.

.NOTES
+--------------------------------------------------------------------------------------------------+
| ORIGIN STORY                                                                                     |
+--------------------------------------------------------------------------------------------------+
|   DATE        : 2016-06-23
|   AUTHOR      : Brandon Warner
|   DESCRIPTION : Initial Draft
+--------------------------------------------------------------------------------------------------+
|   DATE        : 2016-06-27
|   AUTHOR      : Brandon Warner
|   DESCRIPTION : Adjusted final return string to account for change in line endings of last line
                  returned in Get-WordWrappedText
+--------------------------------------------------------------------------------------------------+

.PARAMETER CommentText
The text that we want to appear in the comment block.

.PARAMETER SectionWidth
The target width of the comment block

.PARAMETER TabWidth
The number of characters which the comment block will be indented for each indentation level.

.PARAMETER IndentLevel
The number of indentation levels which we want to indent the text by.

.PARAMETER Encoding
'Unicode' for special Unicode box-drawing characters to be used. 'OEM' for approximate ANSI characters to appear instead.

.PARAMETER CommentStyle
Specifies the comment style for the programming language we are providing the comment block for.

.EXAMPLE

Get-BoxedComment `
  -CommentText 'Line breaking, also known as word wrapping, is the process of breaking a section of text into lines such that it will fit in the available width of a page, window or other display area. In text display, line wrap is the feature of continuing on a new line when a line is full, such that each line fits in the viewable window, allowing text to be read from top to bottom without any horizontal scrolling. Word wrap is the additional feature of most text editors, word processors, and web browsers, of breaking lines between words rather than within words, when possible. Word wrap makes it unnecessary to hard-code newline delimiters within paragraphs, and allows the display of text to adapt flexibly and dynamically to displays of varying sizes.' `
  -IndentLevel 2 `
  -CommentStyle PowerShell | Out-File -FilePath "$env:USERPROFILE\CommentBlockSample.ps1" -Force

#>

function Get-BoxedComment
  {
    [OutputType([String])]    
    [CmdletBinding()]
    Param
      (
          [Parameter(Mandatory = $true)]
          [string]$CommentText  

        , [Parameter(Mandatory = $false)]
          [ValidateRange(25,150)]
          [Int]$SectionWidth = 100

        , [Parameter(Mandatory = $false)]
          [ValidateRange(1,8)]
          [Int]$TabWidth = 2
  
        , [Parameter(Mandatory = $false)]
          [ValidateRange(0,50)]
          [Int]$IndentLevel = 0  

        , [Parameter(Mandatory = $false)]
          [ValidateSet('Unicode','OEM')]
          [String]$Encoding = 'Unicode'  

        , [Parameter(Mandatory = $false)]
          [ValidateSet('SQL','PowerShell')]
          [String]$CommentStyle = 'SQL'  
      )

    <#+--------------------------------------------------------------------+#\
      | Define the box drawing characters                                  |
    \#+--------------------------------------------------------------------+#>  
    switch($Encoding)
      {
        'Unicode' 
          {
            $BoxHorizontalLineChar     = '-'
            $BoxVerticalLineChar       = '|'
            $BoxUpperLeftCornerChar    = '+'
            $BoxUpperRightCornerChar   = '+'
            $BoxLowerLeftCornerChar    = '+'
            $BoxLowerRightCornerChar   = '+'
          }
        'OEM'
          {
            $BoxHorizontalLineChar     = '-'
            $BoxVerticalLineChar       = '|'
            $BoxUpperLeftCornerChar    = '+'
            $BoxUpperRightCornerChar   = '+'
            $BoxLowerLeftCornerChar    = '+'
            $BoxLowerRightCornerChar   = '+'
          }
      }
  
    <#+--------------------------------------------------------------------+#\
      | Define the multi-line commenting delimiters & corresponding        |
      | symmetry embellishments as per the given comment style             |  
    \#+--------------------------------------------------------------------+#>
    switch($CommentStyle)
      {
        'SQL' 
          {
            $MultilineCommentOpen   = '/*'
            $UpperRightEmbelishment = '*\'
            $LowerLeftEmbelishment  = '\*'
            $MultilineCommentClose  = '*/'
          }
        'PowerShell'
          {
            $MultilineCommentOpen   = '<#'
            $UpperRightEmbelishment = '# '
            $LowerLeftEmbelishment  = ' #'
            $MultilineCommentClose  = '#>' 
          }
      }  

    [String]$BoxHorizontalLine  = $BoxHorizontalLineChar.PadRight($SectionWidth-6).Replace(' ',$BoxHorizontalLineChar)
    [String]$IndentSpace        = ''.PadLeft($TabWidth*$IndentLevel)
    [String]$Body               = Get-WordWrappedText `
                                    -Text $CommentText `
                                    -Prefix "$("$BoxVerticalLineChar".PadLeft($MultilineCommentOpen.Length + 1))" `
                                    -Suffix "$BoxVerticalLineChar" `
                                    -WrapWidth ($SectionWidth - $MultilineCommentOpen.Length -1 ) `
                                    -TabWidth $TabWidth `
                                    -IndentLevel $IndentLevel
  
    return @"
$($IndentSpace)$($MultilineCommentOpen)$($BoxUpperLeftCornerChar)$($BoxHorizontalLine)$($BoxUpperRightCornerChar)$($UpperRightEmbelishment)
$($Body)
$($IndentSpace)$($LowerLeftEmbelishment)$($BoxLowerLeftCornerChar)$($BoxHorizontalLine)$($BoxLowerRightCornerChar)$($MultilineCommentClose)
  
"@
  }

<#
.SYNOPSIS
Example for Automatic Programming of Transact-SQL code using SQL Server Management Objects (SMO)

.DESCRIPTION
This function is simple example of using SMO to iterate through some database objects, tables 
and table rows, in this case, in order to generate a T-SQL script. In this example, I have 
created a script that creates a view for each table that selects the top 100 rows of table. 
Though I doubt this would be very useful, this is simply to exemplify the technique of 
looping through the SMO API to generate code. This can be extremely powerful in generating 
a variety of scripts based on property values available in this API.

.NOTES
+----------------------------------------------------------------------------------------------+
| REVISION HISTORY                                                                             |
+----------------------------------------------------------------------------------------------+
|   DATE        : 2016.06.23
|   AUTHOR      : Brandon Warner
|   DESCRIPTION : Initial Draft
+----------------------------------------------------------------------------------------------+

.PARAMETER ServerInstance
Target SQL Server\Instance

.PARAMETER Database
Target Database

.PARAMETER Table
Target Table

.PARAMETER DestinationScriptPath
Destination File Path for the script we want to write

.EXAMPLE 

Write-TsqlScript `
  -ServerInstance 'MyServer\MyInstance,MyListenerPort' `
  -Database 'MyDatabase' `
  -DestinationScriptPath "$env:USERPROFILE\AutomaticProgrammingTSQL_SMO_Example.sql"

#>
function Write-TsqlScript
  {
    [CmdletBinding()]
    Param
      (
          [Parameter(Mandatory=$true)]
          [String]$ServerInstance
  
        , [Parameter(Mandatory=$true)]
          [String]$Database

        , [Parameter(Mandatory=$false)]
          [String]$DestinationScriptPath  
      )

    <#+--------------------------------------------------------------------+#
      | Connect to SQL Server via SMO                                      |
     #+--------------------------------------------------------------------+#>
    $SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance
    $SmoServer.ConnectionContext.Disconnect() | Out-Null
    $SmoServer.ConnectionContext.ApplicationName = 'PowerShell Script'
    $SmoServer.ConnectionContext.LoginSecure = $true
    $SmoServer.ConnectionContext.Connect()

    [String]$ViewName = "dbo.vw_$($Table)_Top100"
    [String]$TSQL     = @"
/*+---------------------------------------------------------------------------------------------+*\
  | TITLE: $("Create `"Top 100`" Views for user tables in database $Database.".PadRight(85))|
  +---------------------------------------------------------------------------------------------+
  | DESCRIPTION:                                                                                |
  |                                                                                             |
  |   Initially Auto-Generated by PowerShell Script:                                            |
$(Get-WordWrappedText `
  -Text ($MyInvocation.ScriptName) `
  -WrapWidth 94 `
  -Prefix '|    ' `
  -Suffix '|' `
  -IndentLevel 1)
  |   Function:                                                                                 |
$(Get-WordWrappedText `
  -Text ($MyInvocation.MyCommand.Name) `
  -WrapWidth 94 `
  -Prefix '|    ' `
  -Suffix '|' `
  -IndentLevel 1)  
  +---------------------------------------------------------------------------------------------+
  | REVISION HISTORY:                                                                           |
  +---------------------------------------------------------------------------------------------+
  |   DATE       AUTHOR          CHANGE DESCRIPTION                                             |
  |   ---------- --------------- ---------------------------------------------------------------+
  |   $((Get-Date).ToString('yyyy.MM.dd')) $($env:USERNAME.PadRight(15)) Initial Draft$("".PadLeft(50))|
\*+---------------------------------------------------------------------------------------------+*/

"@
  
    <#+----------------------------------------------------------------------------------------------+#
      | Loop through all the non-system tables in the given database                                 |
     #+----------------------------------------------------------------------------------------------+#>
    $TargetTables = $SmoServer.Databases[$Database].Tables | where {!$_.IsSystemObject} 
    $TableCount = $TargetTables.Count
  
    [Int]$i = 1
    $TargetTables |
      % {
          $Table = $_.Name
          
          Write-Progress -Activity 'Generating Auto-Programmed TSQL Script' -Status "Scripting for table $Table" -PercentComplete ([Math]::Ceiling(($i/$TableCount)*100.00))
    
          <#+----------------------------------------------------------------------------------------------+#
            | Create comment header-block for this script & other setup before looping through columns     |
           #+----------------------------------------------------------------------------------------------+#>
          [String]$ViewName = "dbo.vw_$($Table)_Top100"
          $TSQL += @"

$(Get-BoxedComment `
  -CommentText "Create View: $ViewName" `
  -IndentLevel 1 `
  -SectionWidth 75 `
  -CommentStyle SQL)
  IF OBJECT_ID('$ViewName','view') IS NOT NULL
   DROP VIEW $ViewName
  GO

  CREATE VIEW
    $ViewName
  AS
  SELECT TOP 100
"@
          [Bool]$IsFirst    = $true
          [Int]$PadLength   = 0
          [Char]$TableAlias = $Table.Substring(0,1).ToLower()
        
          <#+----------------------------------------------------------------------------------------------+#
            | Find the longest column name for code alignment purposes                                     |
           #+----------------------------------------------------------------------------------------------+#>  
          $_.Columns | where {!$_.IsSystemObject} | 
            % { 
                if($_.Name.Length -gt $PadLength) 
                  { 
                    $PadLength = $_.Name.Length 
                  } 
              }
          $PadLength += 2
          if($PadLength % 2 -eq 1) 
            { 
              $PadLength += 1 
            }
        
          <#+----------------------------------------------------------------------------------------------+#
            | Loop through the columns in the table, adding each to the select statement                   |
           #+----------------------------------------------------------------------------------------------+#>  
          $_.Columns | where {!$_.IsSystemObject} | 
            % {
                if($IsFirst)
                  {
                    $TSQL += @"

      $($_.Name.PadRight($PadLength))= $TableAlias.$($_.Name)

"@
                  }
                else
                  {
                    $TSQL += @"
    , $($_.Name.PadRight($PadLength))= $TableAlias.$($_.Name)

"@
                  }
                $IsFirst = $false          
              }

          <#+--------------------------------------------------------------------+#
            | Finalize the create view statement for this table                  |
           #+--------------------------------------------------------------------+#>
          $TSQL += @"
  FROM
    dbo.$Table $TableAlias
  GO

"@    
          $i++
        }
  
    <#+----------------------------------------------------------------------------------------------+#
      | Display the final script to the host terminal and write out the file if a destination file   |
      | path was given                                                                               |
     #+----------------------------------------------------------------------------------------------+#> 
    $TSQL | Out-Host  
    if($DestinationScriptPath)
      {
        $TSQL | Out-File -FilePath $DestinationScriptPath -Force
      }
  }