$arrDispNames = new-object collections.arraylist
$z = 0

for($I=0;$I -lt $Dt.rows.count;$I++) {
   if($arr.DispName.Contains($dt.rows[$i].displayname)) {
        $z = $z + 1
   }else {
      [void]arrDispNames.add($dt.rows[$i].displayname)
   }
}
write-host $z

HostName   DisplayName      EntryDate            Usages
compy1      blahblah         1/1/1000             10000
compy2      blahblah         1/1/1000             10000
compy3      foobar           1/1/1000             10000
compy4      snafu            1/1/1000             10000
compy5      foobar           1/1/1000             10000
compy6      foobar           1/1/1000             10000

adaz fucked around with this message at 00:15 on May 3, 2011

# for CSV export.
$exportTable = @()

# Build an array of our distinct host names.
[array]$arrDistinct = $dt.rows | select-Object HostName -unique

# Loop through each unique address & find number of occurrences.
foreach($hostName in $arrDistinct) {
     $OutObj = "" | select Hostname,Occurrences
     $count = $dt.rows | Where-Object {$_.hostName -match $hostName} | measure-Object 
     $OutObj.hostname = $hostAddr.hostname
     $OutObj.occurrences = $count.count
     $exporttable += $outObj
}

#export our CSV file out
$exportTable | export-Csv C:\temp\hostOcc.csv -noTypeInformation

<#
       .SYNOPSIS

          Passed a DataTable object, counts the number of specified property occurrences and exports out a csv file

      .EXAMPLE

            .\Count-DataTableProperties -Datatable $dt -property hostName -exportPath C:\temp\blah.csv

            Passed $dt it checks for the number of occurrences of the property hostName and exports to c:\temp\blah.csv

      .NOTES

            NAME: Count-DataTableProperties.ps1
            AUTHOR: Adaz
            DATE: 05/04/2011

#Requires -Version 2.0
#>
param(
[parameter(position=0,mandatory = $true)]
$dataTable,
[parameter(position=1,mandatory = $true)]
[string]
$property,
[parameter(position=2,mandatory = $true)]
[string]
$exportPath
)

$exportTable = @()

[array]$arrDistinct = $dataTable.rows | select-Object $property -unique

foreach($Obj in $arrDistinct) {
     $OutObj = "" | select $property,Occurrences
     $count = $dataTable.rows | Where-Object {$_.$($property) -match $obj} | measure-Object 
     $OutObj.$($property) = $obj.$($property)
     $OutObj.occurrences = $count.count
     $exporttable += $outObj
}
$exportTable | export-Csv $exportPath -noTypeInformation

adaz fucked around with this message at 00:07 on May 5, 2011

adaz fucked around with this message at 17:01 on May 5, 2011

param 
(
  [string] $server,
  [string] $database,
  [string] $schema = "dbo",
  [string] $output_directory = "$HOME\Desktop\repos\dump\"
)

# Example call
# C:\SQL\Powershell\Examples\script_data.ps1 ".\sql2005" "tempdb" "dbo" "temp"
# or specify output file
# C:\SQL\Powershell\Examples\script_data.ps1 ".\sql2008" "tempdb" "dbo" "temp" "c:\temp\temp.sql"

if ($server -eq $null)
{
	exit;
}
if ($database -eq $null)
{
	exit;
}

Remove-Item -Force -Recurse -LiteralPath "$output_directory\UserDefinedFunction\"

New-Item -Force -Type Directory -Path "$output_directory\UserDefinedFunction\"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc") | out-null

$srv = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)

# Get the database and table objects
$db = $srv.Databases[$database]
$tables = $db.tables | Where-object { $_.schema -eq $schema } 

# Set scripter options to ensure only schema is scripted
$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $false;
$scripter.Options.NoCommandTerminator = $false; # Include GOs after every script
$Scripter.Options.DriAll=$True; # DRI = "Declarative Referential Integrity"
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

$udfs = $db.UserDefinedFunctions | Where-object { $_.Schema -eq $schema } 

if ($udfs.Count -ne 0)
{
	foreach ($udf in $udfs)
	{
		[Microsoft.SqlServer.Management.Smo.UrnCollection] $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
		$smoObjects.Add($udf.Urn);
		Write-Host "Dumping $udf";
		$output_filename = $output_directory + "\UserDefinedFunction\" + $udf.Name + ".sql";
		$scripter.Options.FileName = $output_filename;
		$Scripter.Options.AppendToFile=$False;
		$Scripter.Options.ScriptDrops=$True;
		$Scripter.Options.IncludeIfNotExists=$True;
		$Scripter.Script([Microsoft.SqlServer.Management.Smo.UrnCollection]$($smoObjects));
		$Scripter.Options.ScriptDrops=$False;
		$Scripter.Options.IncludeIfNotExists=$False;
		$Scripter.Options.AppendToFile=$True
		$Scripter.Options.Permissions = $True;
		$Scripter.Script([Microsoft.SqlServer.Management.Smo.UrnCollection]$($smoObjects));
	}
}

Z-Bo fucked around with this message at 21:27 on May 6, 2011

$Scripter.Script([Microsoft.SqlServer.Management.Smo.UrnCollection]$($smoObjects));

$Scripter.Script($udf);

Multiple ambiguous overloads found for "Script" and the argument count: "1".
At C:\Users\ME\Desktop\dumper\Script-DatabaseObjectsNew.ps1:188 char:19
+         $Scripter.Script <<<< ([Microsoft.SqlServer.Management.Smo.UrnCollection]($($smoObjects)));
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

$params = new-object object[] 1;
$params[0] = $smoObjects;
[Microsoft.SqlServer.Management.SMO.Scripter].GetMethod("Script", [System.Type[]] @([Microsoft.SqlServer.Management.Smo.UrnCollection])) | % { $_.Invoke($scripter, $params) }

Exception calling "Invoke" with "2" argument(s): "Script failed for Server 'MyServerName'. "
At C:\Users\ME\Desktop\dumper\Script-DatabaseObjectsNew.ps1:189 char:155
+         [Microsoft.SqlServer.Management.SMO.Scripter].GetMethod("Script", [System.Type[]] @([Microsoft.SqlServer.Mana
gement.Smo.UrnCollection])) | % { $_.Invoke <<<< ($scripter, $params) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodTargetInvocation

Z-Bo fucked around with this message at 22:15 on May 6, 2011

adaz fucked around with this message at 04:30 on May 7, 2011

Z-Bo fucked around with this message at 15:13 on May 7, 2011

	foreach ($udf in $udfs)
	{
               $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
		$smoObjects.Add($udf.Urn)
		Write-Host "Dumping $udf"
		$output_filename = $output_directory + "\UserDefinedFunction\" + $udf.Name + ".sql"
		$scripter.Options.FileName = $output_filename;
		$Scripter.Options.AppendToFile=$False
		$Scripter.Options.ScriptDrops=$True
		$Scripter.Options.IncludeIfNotExists=$True
		$Scripter.Script($smoObjects)
		$Scripter.Options.ScriptDrops=$False
		$Scripter.Options.IncludeIfNotExists=$False
		$Scripter.Options.AppendToFile=$True
		$Scripter.Options.Permissions = $True
		$Scripter.Script($smoObjects)
	}

adaz fucked around with this message at 22:12 on May 8, 2011

adaz fucked around with this message at 21:42 on May 9, 2011

# Wrap ActualCommand in Invoke-Command, for logging purposes
function Invoke-Command ($argument_1, $argument_2)
{
	if (!$argument_1)
	{
		throw "`$argument_1 is not defined"
	}
	if (!$argument_2)
	{
		throw "`$argument_2 is not defined"
	}

	$invocation = {ActualCommand -Argument1 $argument_1 -Argument2 $argument_2}
	Write-Host "$invocation`r"
	Write-Host "`$argument_1=$argument_1, `$argument_2=$argument_2"
	& $invocation

	if ($LASTEXITCODE -ne 0)
	{
		$LASTEXITCODE
		throw "$invocation`r`$argument_1=$argument_1, `$argument_2=$argument_2`r exited with an error return code: $LASTEXITCODE"
	}
}

• What program did I actually call when I ran this PS function?
  
• What arguments were passed to it?
  
• Did I make any mistakes when calling this PS function?
  
• Did the program complete without errors? If there are errors, use exception handling instead of having to check return codes. Useful for very large scripts Victor and I were writing.
  

function Invoke-Command ($command, $args)

Z-Bo fucked around with this message at 00:43 on May 10, 2011