PowerShell script to generate code do deploy a SQLCLR assembly

GenerateSqlClrAssemblySql.ps1

Param(
	[Parameter(Mandatory=$true)][string] $File,
	[string]$Schema = "dbo",
	[string]$PermissionSet = "safe"
)

Function Parse-Attr($attr) {
	if ($attr) {
		$result = @{ "TypeName" = $attr.AttributeType.Name }
		$attr.Properties | % { $result[$_.Name] = $_.Argument.Value }
		$result
	}
	else {
		$null
	}
}

Function Get-SqlName($item, $attrData) {
	if ($attr.Name) {
		$attr.Name
	}
	else {
		$item.Name
	}
}

Function Get-ParameterSql($param) {
	"@$($param.Name) $(Get-TypeSql $param.ParameterType ($param.CustomAttributes | % { Parse-Attr $_ }))"
}

Function Get-TypeName($type, $attrData) {
	if (-not $attrData) {
		$attrData = $type.CustomAttributes | ? { $_.AttributeType.Namespace -eq "Microsoft.SqlServer.Server" } | % { Parse-Attr $_ }
	}
	
	Get-QualifiedName (Get-SqlName $type $attrData)
}

Function Get-QualifiedName($name) {
	if ($name -match "^(.*?)\.(.*)$") {
		"[$($matches[1])].[$($matches[2])]"
	}
	else {
		"[$Schema].[$name]"
	}
}

Function Get-SimpleTypeSql($type, $attrData) {
	switch ($type.FullName) {
		{ $_ -eq "System.String" -or $_ -eq "System.Data.SqlTypes.SqlString" } {
			if ($attrData.MaxSize) { $maxSize = $attrData.MaxSize } else { $maxSize = 4000 }
			if ($MaxSize -le -0) {
				"nvarchar(max)"
			}
			elseif ($attrData.IsFixedLength) {
				"nchar($maxSize)"
			}
			else {
				"nvarchar($maxSize)"
			}
		}
		"System.Char" { "nchar(1)" }
		{ $_ -eq "System.Single" -or $_ -eq "System.Data.SqlTypes.SqlSingle" } { "real" }
		{ $_ -eq "System.Double" -or $_ -eq "System.Data.SqlTypes.SqlDouble" } { "float" }
		{ $_ -eq "System.Decimal" -or $_ -eq "System.Data.SqlTypes.SqlDecimal" } {
			if ($attrData.Scale -gt 0) { $scale = $attrData.Scale } else { $scale = 0 }
			if ($attrData.Precision -gt 0) { $precision = $attrData.Precision } else { $precision = 18 }
			"numeric($precision, $scale)"
		}
		{ $_ -eq "System.Boolean" -or $_ -eq "System.Data.SqlTypes.SqlBoolean" } { "bit" }
		{ $_ -eq "System.Int64" -or $_ -eq "System.Data.SqlTypes.SqlInt64" } { "bigint" }
		{ $_ -eq "System.Int32" -or $_ -eq "System.Data.SqlTypes.SqlInt32" } { "int" }
		{ $_ -eq "System.Int16" -or $_ -eq "System.Data.SqlTypes.SqlInt16" } { "smallint" }
		{ $_ -eq "System.Byte" -or $_ -eq "System.Data.SqlTypes.SqlByte" } { "tinyint" }
		"System.Object" { "sql_variant" }
		"System.Data.SqlTypes.SqlXml" { "xml" }
		{ $_ -eq "System.Byte[]" -or $_ -eq "System.Data.SqlTypes.SqlBinary" } {
			if ($attrData.MaxSize) { $maxSize = $attrData.MaxSize } else { $maxSize = 8000 }
			if ($maxSize -le 0) {
				"varbinary(max)"
			}
			elseif ($attrData.IsFixedLength) {
				"binary($maxSize)"
			}
			else {
				"varbinary($maxSize)"
			}
		}
		{ $_ -eq "System.Guid" -or $_ -eq "System.Data.SqlTypes.SqlGuid" } { "uniqueidentifier" }
		{ $_ -eq "System.DateTime" -or $_ -eq "System.Data.SqlTypes.SqlDateTime" } { "datetime" }
		"System.Data.SqlTypes.SqlMoney" { "money" }
		default {
			if ($type -is [Mono.Cecil.TypeDefinition]) {
				Get-TypeName $type
			}
			else {
				throw "Unknown argument type $type"
			}
		}
	}
}

Function Get-TypeSql($type, $attrData) {
	if (($type.GenericArguments.Count -eq 1) -and ($type.ElementType.FullName -eq "System.Nullable``1")) {
		Get-TypeSql $type.GenericArguments[0] $attrData
		return
	}
	
	if ($type.IsByReference) {
		"$(Get-SimpleTypeSql $type.ElementType $attrData) OUT"
	}
	else {
		Get-SimpleTypeSql $type $attrData
	}
}

Function Get-CallSpec($method) {
	"AS EXTERNAL NAME [$($asm.Name.Name)].[$($method.DeclaringType.FullName)].[$($method.Name)]"
}

Function Get-ReturnTypeSql($method) {
	"$(Get-TypeSql $method.ReturnType ($method.MethodReturnType.CustomAttributes | % { Parse-Attr $_ }))"
}

Function GenerateFunctionSql($method, $attrData) {
	$name = Get-SqlName $method $attrData
	$tableDefinition = $attr.TableDefinition
	if ($tableDefinition) {
		$returnType = "TABLE ($tableDefinition)"
	}
	else {
		$returnType = Get-ReturnTypeSql $method
	}
	"CREATE FUNCTION $(Get-QualifiedName $name)($(($method.Parameters | % { Get-ParameterSql $_ }) -join ", ")) RETURNS $returnType $(Get-CallSpec $method)"
}

Function GenerateProcedureSql($method, $attrData) {
	$name = Get-SqlName $method $attrData
	if ($method.Parameters.Count -gt 0) {
		$paramspec = "($(($method.Parameters | % { Get-ParameterSql $_ }) -join ", "))"
	}
	else {
		$paramspec = ""
	}
	"CREATE PROCEDURE $(Get-QualifiedName $name)$paramspec $(Get-CallSpec $method)"
}

Function GenerateTriggerSql($method, $attrData) {
	$name = Get-SqlName $method $attrData
	"CREATE TRIGGER $(Get-QualifiedName $name) ON $(Get-QualifiedName($attrData.Target)) $($attrData.Event) $(Get-CallSpec $method)"
}

Function GenerateUserDefinedAggregateSql($type, $attrData) {
	$name    = Get-SqlName $type $attrData
	$params  = $type.Methods | ? { $_.Name -eq "Accumulate" } | % { $_.Parameters } | % { Get-ParameterSql $_ }
	$rettype = $type.Methods | ? { $_.Name -eq "Terminate" } | % { Get-ReturnTypeSql $_ }
	"CREATE AGGREGATE $(Get-QualifiedName $name)($($params -join ", ")) RETURNS $rettype EXTERNAL NAME [$($asm.Name.Name)].[$($type.FullName)]"
}

Function GenerateUserDefinedTypeSql($type, $attrData) {
	"CREATE TYPE $(Get-TypeName $type $attrData) EXTERNAL NAME [$($asm.Name.Name)].[$($type.FullName)]"
}

Function CreateSqlForMethods() {
	process {
		$method = $_
		$attr = $method.CustomAttributes | ? { $_.AttributeType.Namespace -eq "Microsoft.SqlServer.Server" } | % { Parse-Attr $_ }
		if ($attr -ne $null) {
			if ($attr -is [Array]) {
				throw "More than one attribute from the namespace 'Microsoft.SqlServer.Server' on the method $($_.Name)."
			}
			switch ($attr.TypeName) {
				"SqlFunctionAttribute" { GenerateFunctionSql $method $attr }
				"SqlProcedureAttribute" { GenerateProcedureSql $method $attr }
				"SqlTriggerAttribute" { GenerateTriggerSql $method $attr }
				"SqlMethodAttribute" {} # Nothing to do, AFAIK
				default { throw "Unsupported attribute $($attr.AttributeType.Name)" }
			}
		}
	}
}

Function CreateSqlForTypes() {
	process {
		$type = $_
		$attr = $type.CustomAttributes | ? { $_.AttributeType.Namespace -eq "Microsoft.SqlServer.Server" } | % { Parse-Attr $_ }
		if ($attr -ne $null) {
			if ($attr -is [Array]) {
				throw "More than one attribute from the namespace 'Microsoft.SqlServer.Server' on the type $($_.FullName)."
			}
			switch ($attr.TypeName) {
				"SqlUserDefinedTypeAttribute" { GenerateUserDefinedTypeSql $type $attr }
				"SqlUserDefinedAggregateAttribute" { GenerateUserDefinedAggregateSql $type $attr }
				default { throw "Unsupported attribute $($attr.AttributeType.Name)" }
			}
		}
		$_.Methods | CreateSqlForMethods
	}
}

Function BinaryContentToHex([string]$File, [int]$MaxBytesPerLine = [int]::MaxValue) {
	$content = [System.IO.File]::ReadAllBytes($File)
	$sb = New-Object System.Text.StringBuilder
	$index = 0
	while ($index -lt $content.Length) {
		if ($index -gt 0) {
			$sb.AppendLine("\") > $null
		}
		$sb.AppendFormat([System.BitConverter]::ToString($content, $index, [System.Math]::Min($content.Length - $index, $MaxBytesPerLine)) -replace "-", "") > $null
		$index += $MaxBytesPerLine
	}
	$sb.ToString()
}

Import-Module .\Mono.Cecil.dll
$resolvedPath = Resolve-Path "$File"
$asm = [Mono.Cecil.AssemblyDefinition]::ReadAssembly($resolvedPath)

@"
CREATE ASSEMBLY [$($asm.Name.Name)] AUTHORIZATION dbo FROM
0x$(BinaryContentToHex $resolvedPath -MaxBytesPerLine 1000)
WITH PERMISSION_SET = $($PermissionSet.ToUpper())
"@

$asm | % { $_.Modules } | % { $_.Types } | CreateSqlForTypes