Commands / Find-sqmDatabaseObject
Find-sqmDatabaseObject
Database sqmSQLTool v1.8.2+ · Query
Searches user (or system) databases on an instance for objects matching a name pattern. Returns database, schema, object type (TABLE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM) and — optionally — a preview of the object definition for matches in SQL code.

Searches are performed on sys.objects (by name) and optionally sys.sql_modules (by definition text). Database and object-type filters support wildcards.

Parameters

ParameterTypeRequiredDefaultNotes
-ObjectNamestringRequired—Object name or wildcard pattern (e.g. sp_GetOrders, *log*).
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional—Credentials for the connection.
-ObjectTypestring[]Optional—Restrict to types: TABLE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM. Multiple allowed.
-DatabasestringOptional* = all user DBsDatabase name or wildcard pattern (e.g. Sales*).
-IncludeSystemswitchSwitch$falseInclude system databases (master, model, msdb).
-SearchDefinitionswitchSwitch$falseAlso search object definitions (slower).
-EnableExceptionswitchSwitch$falseRe-throw exceptions instead of logging warnings.

Execution Flow

START dbatools available? NO throw: dbatools not found YES Get-DbaDatabase (exclude system unless -IncludeSystem) Apply -Database wildcard filter via -like DBs found after filter? NO log WARNING return empty YES Build T-SQL query: sys.objects + (optional) sys.sql_modules Apply ObjectType IN filter and ObjectName LIKE filter foreach $db in $dbList Invoke-DbaQuery -Database $db.Name -Query $query For each row: add [PSCustomObject] to results SqlInstance, Database, Schema, ObjectName, ObjectType, DefinitionPreview catch per-DB: log WARNING (continue if -EnableException) continue to next DB Return $allResults [PSCustomObject[]] object search results DONE

Examples

Find a stored procedure by name
Find-sqmDatabaseObject -SqlInstance "SQL01" -ObjectName "sp_GetOrders"
Search for all objects with name containing 'log' (tables and views only)
Find-sqmDatabaseObject -SqlInstance "SQL01" -ObjectName "*log*" -ObjectType "TABLE","VIEW" -Database "Sales*"
Search object definitions (in addition to names)
Find-sqmDatabaseObject -SqlInstance "SQL01" -ObjectName "old_table_name" -SearchDefinition
← Back
Command Index