Find-DbaView

Want to see the source code for this command? Check out Find-DbaView on GitHub.
Want to see the Bill Of Health for this command? Check out Find-DbaView.

Synopsis

Searches database views for specific text patterns or regular expressions in their definitions.

Description

Scans view definitions across one or more databases to locate specific text patterns, table references, or code constructs. This helps DBAs identify views that reference particular tables before schema changes, find views containing sensitive data patterns like email addresses or SSNs, or locate views with specific business logic during troubleshooting. The function searches the actual view definition text (TextBody) and returns the matching views along with line numbers showing exactly where the pattern was found, making it easy to understand the context of each match.

Syntax

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

Examples

Example: 1

PS C:\> Find-DbaView -SqlInstance DEV01 -Pattern whatever

Searches all user databases views for "whatever" in the text body

Example: 2

PS C:\> Find-DbaView -SqlInstance sql2016 -Pattern '\w+@\w+\.\w+'

Searches all databases for all views that contain a valid email pattern in the text body

Example: 3

PS C:\> Find-DbaView -SqlInstance DEV01 -Database MyDB -Pattern 'some string' -Verbose

Searches in "mydb" database views for "some string" in the text body

Example: 4

PS C:\> Find-DbaView -SqlInstance sql2016 -Database MyDB -Pattern RUNTIME -IncludeSystemObjects

Searches in "mydb" database views for "runtime" in the text body

Required Parameters

-SqlInstance

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

-Pattern

Specifies the text pattern or regular expression to search for within view definitions. Supports full regex syntax for complex pattern matching.
Use this to find views referencing specific tables before schema changes, locate sensitive data patterns like email addresses or SSNs, or identify views containing particular business logic.
Common patterns include table names, column references, function calls, or data validation expressions.

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 search for views containing the pattern. Accepts wildcards and multiple database names.
Use this when you need to limit the search scope to specific databases instead of scanning all databases on the instance.
Particularly useful for large instances where you only need to check certain application databases.

-ExcludeDatabase

Specifies databases to skip during the view search operation. Accepts multiple database names.
Use this to exclude large databases that you know don't contain relevant views, speeding up the search process.
Common exclusions include development copies, archive databases, or third-party application databases.

-IncludeSystemObjects

Includes system views in the search operation alongside user-created views. System views are excluded by default.
Use this when troubleshooting issues that might involve system view dependencies or when documenting complete database schemas.
Warning: Including system views significantly slows down the search, especially when scanning multiple databases or large instances.

-IncludeSystemDatabases

Includes system databases (master, model, msdb, tempdb) in the view search operation. System databases are excluded by default.
Use this when investigating SQL Server internals, troubleshooting replication issues, or documenting complete instance configurations.
Most DBA tasks focus on user databases, so this parameter is typically used for advanced troubleshooting scenarios.

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