Get-DbaDependency

Synopsis

Maps SQL Server object dependencies and generates creation scripts in proper deployment order

Description

This function discovers SQL Server object dependencies using SMO (SQL Server Management Objects) and returns detailed information including creation scripts and deployment order.
By default, it finds all objects that depend on your input object - perfect for impact analysis before making changes or understanding what might break if you modify something.

The function returns objects in hierarchical tiers, showing you exactly which objects need to be created first when deploying to a new environment.
Each result includes the T-SQL creation script, so you can generate deployment scripts in the correct dependency order without manually figuring out prerequisites.

Use the ‘Parents’ switch to reverse the direction and find what your object depends on instead - useful for understanding all the prerequisites needed before creating or moving an object.
This is particularly valuable when migrating individual objects between environments or troubleshooting missing dependencies.

For more details on dependency relationships, see:
https://technet.microsoft.com/en-us/library/ms345449(v=sql.105).aspx

Syntax

Get-DbaDependency
    [[-InputObject] <Object>]
    [-AllowSystemObjects]
    [-Parents]
    [-IncludeSelf]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> $table = (Get-DbaDatabase -SqlInstance sql2012 -Database Northwind).tables | Where-Object Name -eq Customers
PS C:\> $table | Get-DbaDependency

Returns everything that depends on the “Customers” table

Optional Parameters

-InputObject

Specifies the SQL Server object (table, view, stored procedure, function, etc.) to analyze for dependencies.
Accepts any SMO object from Get-DbaDatabase, Get-DbaDbTable, Get-DbaDbStoredProcedure, and similar commands.
Use this when you need to understand what objects will be affected by changes to a specific database object.

-AllowSystemObjects

Includes system objects like sys tables, system functions, and built-in stored procedures in dependency results.
Use this when you need complete dependency mapping including SQL Server internal objects.
Most DBAs can leave this off since system dependencies rarely impact deployment or migration planning.

-Parents

Reverses the dependency direction to show what objects the input depends on rather than what depends on it.
Essential for understanding prerequisites when migrating objects or troubleshooting “object not found” errors.
Use this to identify all dependencies that must exist before you can create or restore the target object.

-IncludeSelf

Includes the original input object in the results along with its dependencies.
Helpful when generating complete deployment scripts that need to recreate both the object and everything it depends on.
Commonly used when exporting database schemas or preparing objects for cross-environment deployment.

-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

Dataplat.Dbatools.Database.Dependency

Returns one object per dependent object (or prerequisite if using -Parents switch). Objects are sorted by deployment tier, with tier 1 being the lowest-level dependencies that must be created first.

Properties:

• ComputerName: The name of the SQL Server computer
• ServiceName: The SQL Server service name
• SqlInstance: The full SQL Server instance name
• Dependent: The name of the dependent database object
• Type: The SMO object type (Table, View, StoredProcedure, UserDefinedFunction, etc.)
• Owner: The schema/owner of the object
• IsSchemaBound: Boolean indicating if the object is schema-bound (relevant for views and functions)
• Parent: The name of the parent object (the object being depended upon or depending on this object)
• ParentType: The SMO type of the parent object
• Tier: Integer indicating the deployment tier (1 = base dependencies, higher numbers = dependent on lower-numbered tiers)
• Object: The SMO object instance for the dependent object (allows access to all SMO properties)
• Urn: The URN (Uniform Resource Name) of the dependent object
• OriginalResource: The original input object being analyzed for dependencies
• Script: The T-SQL creation script for the dependent object, ready for deployment When -Parents switch is used, Tier values are negative (e.g., -1, -2) to indicate prerequisite dependencies rather than dependent objects. When -IncludeSelf is used, the original input object is included in the results with Tier 0.