-
-
Notifications
You must be signed in to change notification settings - Fork 794
/
Get-DbaDbUserDefinedTableType.ps1
108 lines (82 loc) · 4.23 KB
/
Get-DbaDbUserDefinedTableType.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
function Get-DbaDbUserDefinedTableType {
<#
.SYNOPSIS
Gets database user defined table types
.DESCRIPTION
Gets database user defined table types
.PARAMETER SqlInstance
The target SQL Server instance or instances
.PARAMETER SqlCredential
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
.PARAMETER Database
To get Stored Procedures from specific database(s)
.PARAMETER ExcludeDatabase
The database(s) to exclude - this list is auto populated from the server
.PARAMETER Type
[OPTIONAL] When provided, the output will be filtered to return only the types given otherwise returns all the table types.
.PARAMETER EnableException
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
.NOTES
Tags: Database, UserDefinedTableType, Type
Author: Ant Green (@ant_green)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
.LINK
https://dbatools.io/Get-DbaDbUserDefinedTableType
.EXAMPLE
PS C:\> Get-DbaDbUserDefinedTableType -SqlInstance sql2016
Gets all database user defined table types in all the databases
.EXAMPLE
PS C:\> Get-DbaDbUserDefinedTableType -SqlInstance Server1 -Database db1
Gets all the user defined table types for the db1 database
.EXAMPLE
PS C:\> Get-DbaDbUserDefinedTableType -SqlInstance Server1 -Database db1 -Type type1
Gets type1 user defined table type from db1 database
#>
[CmdletBinding()]
param (
[Parameter(ValueFromPipeline)]
[DbaInstanceParameter[]]$SqlInstance,
[PSCredential]$SqlCredential,
[object[]]$Database,
[object[]]$ExcludeDatabase,
[string[]]$Type,
[switch]$EnableException
)
process {
if (Test-Bound SqlInstance) {
$InputObject = Get-DbaDatabase -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -ExcludeDatabase $ExcludeDatabase
}
foreach ($db in $InputObject) {
if (!$db.IsAccessible) {
Write-Message -Level Warning -Message "Database $db is not accessible. Skipping."
continue
}
if ($db.UserDefinedTableTypes.Count -eq 0) {
Write-Message -Message "No User Defined Table Types exist in the $db database on $instance" -Target $db -Level Output
continue
}
if ($Type) {
$userDefinedTableTypes = $db.UserDefinedTableTypes | Where-Object Name -in $Type
} else {
$userDefinedTableTypes = $db.UserDefinedTableTypes
}
foreach ($tabletype in $userDefinedTableTypes) {
if ( $tabletype.IsSystemObject ) {
continue
}
Add-Member -Force -InputObject $tabletype -MemberType NoteProperty -Name ComputerName -value $tabletype.Parent.ComputerName
Add-Member -Force -InputObject $tabletype -MemberType NoteProperty -Name InstanceName -value $tabletype.Parent.InstanceName
Add-Member -Force -InputObject $tabletype -MemberType NoteProperty -Name SqlInstance -value $tabletype.Parent.SqlInstance
Add-Member -Force -InputObject $tabletype -MemberType NoteProperty -Name Database -value $db.Name
$defaults = ('ComputerName', 'InstanceName', 'SqlInstance' , 'Database' , 'ID', 'Name', 'Columns', 'Owner', 'CreateDate', 'IsSystemObject', 'Version')
Select-DefaultView -InputObject $tabletype -Property $defaults
}
}
}
}