Dependencies between database objects
dictate the order of deployment in code releases. The order of object deployment is key to the success of database
projects, especially when code is moved
between different environments. Therefore, the dependency information viewed
by developers and DBAs must be accurate.
Although SQL Server 2005 and SQL
Server 2000 have many great features,
they still don't track object dependencies
in a reliable fashion. Interestingly, nearly
all third-party dependency tools rely on
the limited information recorded by SQL
Server, which can lead to functional and
deployment errors. To overcome these
deficiencies, I created a dependency
checker that returns the dependency
and deployment order of objects. This
checker uses a
simple algorithm
and some T-SQL
code. Before I
show you how the dependency checker
works and how to use it, let's look at
why SQL Server's built-in dependency
tracking functionality doesn't work well
and how the dependency checker avoids
the same problems.
SQL Server's Deficiencies in
Detecting Dependencies
From the application developers' view,
SQL Server contains four standard object
types: tables, views, stored procedures,
and functions. All other objects are either prerequisites or attributes of objects in
these groups. For example, logins, users,
rules, and full-text catalogs are prerequisites, whereas triggers, foreign keys,
and synonyms are attributes because
they're typically created together with the
parent object. When database projects are
deployed, all prerequisites are created first.
Hence, the main challenge is to identify
dependencies and determine the deployment order of standard objects. With this
goal in mind, let's concentrate on finding
all possible dependencies between tables,
views, stored procedures, and functions,
potentially through their child objects.
Dependencies are expressed in many
ways. A stored procedure that selects
records from a table or view depends
on underlying objects. Similarly, a view
that selects data from a table or function
depends on underlying objects. Another
example is a trigger that manipulates data
in table1 when data updates are made to
table2; table1 therefore depends on table2.
SQL Server 2000 uses the system table
sysdepends to store object dependencies. In
SQL Server 2005, dependency information is recorded in the system view sys.sql
_dependencies. Both of them operate in a
similar manner and map an object's ID
to each dependent's ID. Explicitly, sysdepends contains the columns [id] and
[depid], whereas sys.sql_dependencies uses
[object_id] and [referenced_major_id] to
denote the relationship between an object
and its dependent.
Two problems arise with the built-in dependency tracking functionality
in SQL Server. First, dependencies are
recorded only when the dependent item
already exists in the underlying database.
However, in most cases, nothing prevents
users from creating an object that depends
on another object when the latter doesn't
yet exist, in which case SQL Server
can't detect the dependency. Second, if a
child of one object (e.g., a trigger called
TRG_table1_AfterUpdate that is invoked
after data updates to table1) depends on
another object (e.g., the trigger manipulates data in table2), the parent object
table1 depends on table2.
SQL Server's dependency tracking
functionality doesn't account for these
scenarios. Thus, if a user generates scripts
that contain the schema of such objects
by using popular auto-scripting tools, the
order of operations in the scripts is incorrect. Consider the following examples:
Example 1: A stored procedure that
references a table is created before the table,
as Listing 1. In this case,
each call to execute the stored procedure
before the table is created results in errors.
Example 2: A trigger updates a target
table when data changes are made to the
trigger's parent table, but the trigger is created before the target table, as Listing 2 shows. Because the target table
isn't available in the database (i.e., it hasn't
been created yet), all data updates to the
parent table incur errors.
Example 3: A view selects data from
another view that doesn't exist in the
underlying database, as Listing 3 shows. All
attempts to select data from the non-existent
view will result in binding errors.
To avoid such problems, the dependency
checker analyzes databases for the following
dependencies:
- Tables that depend on other tables.
Cross-table dependencies can occur
when a table with a foreign-key constraint depends on the referenced
primary table. Cross-table dependencies can also occur when the child of
one table depends on a second table, as
illustrated by Listing 2.
- Tables that depend on stored procedures, functions, or views. If a trigger
selects records from views or functions, or if the trigger code executes functions or stored procedures, then the parent table depends on the
referenced objects.
- Views, stored procedures,
or functions that depend
on other objects. This type of dependency can occur when views
select rows from tables, functions, or
other views. Similarly, it can occur when
stored procedures or functions select
data from the same set of objects or call
other stored procedures or functions.
- Objects that depend on themselves. A
table that has a self-referencing foreign key constraint is considered to be self dependent. Other self-dependencies
include a trigger that updates its parent
table and a stored procedure or function that calls itself. For the purpose of
project deployment, self-dependencies
are of little interest, although they're
still taken under consideration by the
dependency checker's algorithm.
How the Checker Works
Foreign-key constraints are stored appropriately in SQL Server. However, dependency
problems arise in all other cases in which
relationships are embedded in the object
text or in the code of child objects. Consequently, it seems natural to use a text-based
dependency checker that parses T-SQL code
to search for object references.
For the purposes of the dependency
checker, let's define a text-based object as
any single view, stored procedure, function,
or trigger. One text-based object depends on
another text-based object when the dependent object's name is referenced in the T-SQL
code. Moreover, when an object is referenced,
the first character before and after the object's
name must be one of the values listed in Table 1. It's also worth noting that if a left square
bracket appears before the object's name, the
object name is most likely appended with a
right square bracket.