Get-DbaDbUserDefinedTableType

Synopsis

Retrieves user-defined table types from SQL Server databases

Description

Retrieves user-defined table types from SQL Server databases, which are custom data types used as table-valued parameters in stored procedures and functions. This command helps DBAs audit these schema-bound objects, document their structure and usage, or identify dependencies before making database changes. Returns detailed information including column definitions, ownership, and creation dates across multiple databases and instances.

Syntax

Get-DbaDbUserDefinedTableType
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [[-Type] <String[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbUserDefinedTableType -SqlInstance sql2016

Gets all database user defined table types in all the databases

Example: 2

PS C:\> Get-DbaDbUserDefinedTableType -SqlInstance Server1 -Database db1

Gets all the user defined table types for the db1 database

Example: 3

PS C:\> Get-DbaDbUserDefinedTableType -SqlInstance Server1 -Database db1 -Type type1

Gets type1 user defined table type from db1 database

Optional Parameters

-SqlInstance

The target SQL Server instance or instances

-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.

-Database

Specifies which databases to retrieve user-defined table types from. Accepts database names and supports wildcards for pattern matching.
Use this when you need to examine table types in specific databases rather than all databases on the instance.

-ExcludeDatabase

Specifies databases to exclude from the search for user-defined table types. Accepts database names and wildcards.
Use this when you want to scan most databases but skip specific ones like system databases or inactive databases.

-Type

Filters results to include only specific user-defined table type names. Accepts an array of type names for multiple selections.
Use this when you need to examine particular table types across databases, such as auditing usage of a specific custom type.

-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.