Get-DbaDbUdf

Synopsis

Retrieves User Defined Functions and User Defined Aggregates from SQL Server databases with filtering and metadata

Description

Retrieves all User Defined Functions (UDFs) and User Defined Aggregates from one or more SQL Server databases, returning detailed metadata including schema, creation dates, and data types. This function helps DBAs inventory custom database logic, analyze code dependencies during migrations, and audit user-created functions for security or performance reviews. You can filter results by database, schema, or function name, and exclude system functions to focus on custom business logic.

Syntax

Get-DbaDbUdf
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [-ExcludeSystemUdf]
    [[-Schema] <String[]>]
    [[-ExcludeSchema] <String[]>]
    [[-Name] <String[]>]
    [[-ExcludeName] <String[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbUdf -SqlInstance sql2016

Gets all database User Defined Functions and User Defined Aggregates

Example: 2

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

Gets the User Defined Functions and User Defined Aggregates for the db1 database

Example: 3

PS C:\> Get-DbaDbUdf -SqlInstance Server1 -ExcludeDatabase db1

Gets the User Defined Functions and User Defined Aggregates for all databases except db1

Example: 4

PS C:\> Get-DbaDbUdf -SqlInstance Server1 -ExcludeSystemUdf

Gets the User Defined Functions and User Defined Aggregates for all databases that are not system objects (there can be 100+ system User Defined Functions in each DB)

Example: 5

PS C:\> 'Sql1','Sql2/sqlexpress' | Get-DbaDbUdf

Gets the User Defined Functions and User Defined Aggregates for the databases on Sql1 and Sql2/sqlexpress

Required Parameters

-SqlInstance

The target SQL Server instance or instances

Optional Parameters

-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 Functions from. Accepts wildcards for pattern matching.
Use this when you need to audit UDFs in specific databases rather than scanning the entire instance.

-ExcludeDatabase

Specifies databases to skip when retrieving User Defined Functions. Useful for excluding system databases or databases under maintenance.
Commonly used to exclude tempdb, model, or large databases that don’t contain custom business logic.

-ExcludeSystemUdf

Filters out built-in SQL Server system functions from the results, showing only custom user-created functions.
Essential when auditing business logic since system databases can contain 100+ built-in UDFs that obscure custom code.

-Schema

Limits results to User Defined Functions within specific schemas. Accepts multiple schema names.
Useful for focusing on functions owned by particular applications or development teams, such as ‘Sales’ or ‘Reporting’ schemas.

-ExcludeSchema

Excludes User Defined Functions from specific schemas when retrieving results.
Helpful for filtering out legacy schemas, test schemas, or third-party application schemas that aren’t relevant to your analysis.

-Name

Retrieves specific User Defined Functions by name. Accepts multiple function names and supports wildcards.
Use this when searching for particular functions during troubleshooting or when documenting specific business logic components.

-ExcludeName

Excludes specific User Defined Functions from results by name. Supports wildcards for pattern matching.
Useful for filtering out known test functions, deprecated functions, or utility functions that clutter audit reports.

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

Outputs

Microsoft.SqlServer.Management.Smo.UserDefinedFunction, Microsoft.SqlServer.Management.Smo.UserDefinedAggregate

Returns one object per User Defined Function or User Defined Aggregate found in the specified databases. Both SMO object types are combined in a single output stream, with UserDefinedAggregates always being user-created (no system aggregates exist in SQL Server).

Default display properties (via Select-DefaultView):

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance)
• Database: The name of the database containing the function/aggregate
• Schema: The schema that contains the function/aggregate
• CreateDate: DateTime when the function/aggregate was originally created
• DateLastModified: DateTime of the most recent modification to the function/aggregate
• Name: The name of the User Defined Function or User Defined Aggregate
• DataType: The return data type of the function/aggregate (for example, ‘int’, ‘varchar’, ’table’, etc.)

Additional properties available from SMO (UserDefinedFunction):

• IsSystemObject: Boolean indicating if this is a system-created object (True for system functions, False for user-created)
• AssemblyName: Name of the .NET assembly if this is a CLR-based function
• ClassName: Class name within the assembly for CLR-based functions
• ExecutionContext: Whether function executes in caller or owner context
• IsInlineTableValuedFunction: Boolean for inline table-valued functions
• IsSqlTabular: Boolean indicating if this is a SQL table-valued function
• QuotedIdentifierStatus: Boolean indicating quoted identifier setting
• ReturnsNullOnNullInput: Boolean indicating NULL handling behavior
• Text: The T-SQL source code or assembly reference of the function Note: UserDefinedAggregate objects do not have the IsSystemObject property. The -ExcludeSystemUdf switch filters out system functions but does not affect aggregates (which are never system objects).