--- title: "Find-DbaObject" description: "Searches database objects by name or column name across SQL Server databases using regex patterns." url: "https://dbatools.io/Find-DbaObject/" availability: "Windows, Linux, macOS" tags: ["Object", "Lookup", "Find"] author: "the dbatools team + Claude" source: "https://github.com/dataplat/dbatools/blob/master/public/Find-DbaObject.ps1" last_updated: "2024-01-01" --- # 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 ```powershell Find-DbaObject [-SqlInstance] [[-SqlCredential] ] [[-Database] ] [[-ExcludeDatabase] ] [-Pattern] [[-ObjectType] ] [-IncludeColumns] [-IncludeSystemObjects] [-IncludeSystemDatabases] [-EnableException] [] ``` ## Examples ### Example 1: Searches all user databases on DEV01 for any object whose name contains "Service" ```powershell PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern Service ``` ### Example 2: Searches all user databases on DEV01 for objects named with "Service" and tables/views that have columns... ```powershell 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: Finds all user tables on DEV01 whose names start with "Customer" ```powershell PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern "^Customer" -ObjectType Table ``` ### Example 4: Searches the Accounting database for objects and columns related to "Invoice" ```powershell PS C:\> Find-DbaObject -SqlInstance DEV01 -Pattern "Invoice" -Database Accounting -IncludeColumns ``` ### Example 5: Finds all tables and views whose names contain either "Service" or "Product" ```powershell PS C:\> Find-DbaObject -SqlInstance sql2019 -Pattern "Service|Product" -ObjectType Table, View ``` ### Required Parameters ##### -SqlInstance The target SQL Server instance or instances. This can be a collection and receive pipeline input. | Property | Value | | --- | --- | | Alias | | | Required | True | | Pipeline | true (ByValue) | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | True | | Pipeline | false | | Default Value | | ### 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -ExcludeDatabase Specifies databases to skip during the search. Accepts multiple database names. Use this to exclude large databases or test environments from the search. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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) | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | @("All") | | Accepted Values | Table,View,StoredProcedure,ScalarFunction,TableValuedFunction,Synonym,Trigger,All | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -IncludeSystemObjects Includes system objects (those shipped with SQL Server) in the search results. By default, only user-created objects are searched. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -IncludeSystemDatabases Includes system databases (master, model, msdb, tempdb) in the search scope. By default, only user databases are searched. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ## 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 --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/