Find-DbaObject

Synopsis

Searches database objects by name or column name across SQL Server databases using regex patterns.

Description

Provides a unified search across all database object types (tables, views, stored procedures, functions,
synonyms, triggers) by matching their names against a regex pattern. Optionally extends the search to
column names within tables and views. This complements the existing Find-DbaStoredProcedure, Find-DbaView,
and Find-DbaTrigger commands which search object definition text rather than object or column names.

Uses T-SQL queries against sys.objects and sys.columns for optimal performance. Pattern matching is
performed in PowerShell using full regex syntax.

Syntax

Find-DbaObject
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [-Pattern] <String>
    [[-ObjectType] <String[]>]
    [-IncludeColumns]
    [-IncludeSystemObjects]
    [-IncludeSystemDatabases]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern Service

Searches all user databases on DEV01 for any object whose name contains “Service”.

Example: 2

PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern Service -IncludeColumns

Searches all user databases on DEV01 for objects named with “Service” and tables/views
that have columns whose names contain “Service”.

Example: 3

PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern "^Customer" -ObjectType Table

Finds all user tables on DEV01 whose names start with “Customer”.

Example: 4

PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern "Invoice" -Database Accounting -IncludeColumns

Searches the Accounting database for objects and columns related to “Invoice”.

Example: 5

PS C:\> Find-DbaObject -SqlInstance sql2019 -Pattern "Service|Product" -ObjectType Table, View

Finds all tables and views whose names contain either “Service” or “Product”.

Required Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input.

-Pattern

The regular expression pattern to match against object names (and optionally column names).
Supports full regex syntax for complex pattern matching. For example, use “^Customer” to find objects
starting with “Customer”, or “Service|Product” to find objects mentioning either term.

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 one or more databases to search. When omitted, searches all user databases on the instance.
Use this to focus searches on specific databases when you know where the objects are located.

-ExcludeDatabase

Specifies databases to skip during the search. Accepts multiple database names.
Use this to exclude large databases or test environments from the search.

-ObjectType

Filters the search to specific object types. Accepts one or more of:

• Table: User tables (sys.objects type U)
• View: Views (sys.objects type V)
• StoredProcedure: Stored procedures (sys.objects type P)
• ScalarFunction: Scalar-valued functions (sys.objects type FN)
• TableValuedFunction: Inline and multi-statement table-valued functions (sys.objects type IF/TF)
• Synonym: Synonyms (sys.objects type SN)
• Trigger: Object-level DML triggers plus database DDL SQL triggers
• All: All of the above (default)

-IncludeColumns

When specified, additionally searches column names within tables and views for the given pattern.
Results with column name matches include a MatchType of “ColumnName” and the matching column name.
This is useful for finding which tables or views contain a column related to a specific domain concept.

-IncludeSystemObjects

Includes system objects (those shipped with SQL Server) in the search results.
By default, only user-created objects are searched.

-IncludeSystemDatabases

Includes system databases (master, model, msdb, tempdb) in the search scope.
By default, only user databases are searched.

-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

PSCustomObject

Returns one object per match found. When -IncludeColumns is used, there may be multiple results per database object (one for the object name match plus one per matching column name).

Properties:

• ComputerName: The computer name of the SQL Server instance
• SqlInstance: The SQL Server instance name
• Database: The database containing the matched object
• Schema: The schema of the matched object (null for database DDL triggers)
• Name: The name of the matched object
• ObjectType: The SQL Server type description (e.g., USER_TABLE, VIEW, SQL_STORED_PROCEDURE)
• MatchType: “ObjectName” when the object name matched, “ColumnName” when a column name matched
• ColumnName: The matching column name when MatchType is “ColumnName”, otherwise null
• CreateDate: DateTime when the object was created
• LastModified: DateTime when the object was last modified