The dependency checker operates by
collecting all text-based objects in the
database as requested by the user. Input
and configuration parameters let the user process all database objects or just specify a
collection of items. Next, the dependency
checker retrieves the CREATE statements
for all objects by querying sys.sql_definitions in SQL Server 2005 or syscomments
in SQL Server 2000. The checker stores the
statements in a temporary table.
To find references to other objects in the
text, all T-SQL code comments are stripped
out. The dependency checker does this by
finding instances of strings that start with the
-- or /* comment character, deleting each
comment, and storing the resulting code in
the same temporary table.
At this point, the functional raw code is
at hand and the search process can begin.
The search relies on the set of characters
in Table 1. Explicitly, the algorithm uses
a table called #tblTextBasedObjects that
contains the columns ObjectID (INT) and
ObjectTextWithoutComments (NTEXT
or NVARCHAR(MAX)), which store the
ID and the comment-free CREATE statement for each object, respectively. Furthermore, the algorithm uses a temporary table
named #tblAllDBRoutinesTablesViews to
store all standard objects in the database. This
table contains four columns:
- ObjectName of type NVARCHAR(128)
- ObjectID of type INT
- ObjectNameForLikeSearches1 of type
NVARCHAR(200)
- ObjectNameForLikeSearches2 of type
NVARCHAR(200)
The last two columns are used for string-matching purposes and are populated using
the code that Listing 4 shows. The dependency checker can now search for object
references in a straightforward manner by
applying the LIKE function on the patterns in #tblAllDBRoutinesTablesViews, as Listing 5 shows.
The code in Listings 4 and 5 illustrate
the basic concept behind the dependency
checker; the complete algorithm and code
are a lot longer. You can download the complete algorithm and code, which includes
additional details, from the SQL Server
Magazine Web site.
How to Use the Checker
When you download the code, you'll find
two files: DependencyViewer2K5.sql, which
is the dependency checker for SQL Server
2005, and DependencyViewer2K.sql, which
is the dependency checker for SQL Server
2000. Here are the main parameters for
both scripts:
- @IncludeAllDBObjects. The @
IncludeAllDBObjects option tells the
script whether to consider all objects
in the database (value of 1) or only
user-provided objects (value of 0). If
you set this parameter to 0, you must
populate the temporary table #tblRequestedObjects with the names of all
the objects you want examined.
- @IncludeDependencies. If you set the
@IncludeAllDBObjects parameter to
0 and provide the object names in the
#tblRequestedObjects table, you can
set the @IncludeDependencies option.
Setting this option to 1 tells the script
to find objects that the items specified
in #tblRequestedObjects rely on.
- @IncludeDependants. Like the
@IncludeDependencies option, the
@IncludeDependants option is available if you set @IncludeAllDBObjects
to 0 and provide the object names in
#tblRequestedObjects. Setting this
option to 1 tells the script to find
objects that depend on those items
listed in #tblRequestedObjects.
There are additional options that you can
set. The scripts' usage instructions describe
them in detail. When you use the scripts,
keep the following considerations in mind:
Circular dependencies. Circular dependencies can occur when each item in a group
of objects depend on each other. When
checking for dependencies, it's important
for you to recognize circular relationships.
However, for deployment purposes, there's
no right or wrong ordering; items that fall
under this category can be deployed in an
arbitrary order.
False positives and false negatives. False
positives refer to instances in which the
algorithm reports dependencies that don't
actually exist. These events occur when
objects' names contain the special characters
listed in Table 1. For example, if a database
contains the [sales orders customers] and
[orders] tables and a stored procedure references the [orders] table, the algorithm will
falsely report a dependency on the [sales
orders customers] table as well. In spite of
the possibility of false positives, they don't
affect the correctness of the deployment order and therefore are insignificant. But this
isn't the case with false negatives.
False negatives refer to instances when
dependencies remain undetected. This
occurs in several situations, such as when
a text-based object is created with the
ENCRYPTION option enabled (in which
case, the dependency checker doesn't process the body text) or when the referenced
object's name is noncontiguous. False negatives can also occur when several objects
have the same name yet belong to different
schemas, in which case the DBA responsible
for the database will run into much bigger
problems than identifying dependencies. To
conclude, false negatives can happen in rare
scenarios, but SQL Server and other tools
also overlook these dependencies.
Size limitation. The dependency checker
works well for analyzing dependencies and
deployment orders for dozens or even
hundreds of objects. However, because it's
written in T-SQL, which isn't too efficient
in string manipulations, it won't work well
for databases with large numbers of objects.
These performance issues can be resolved
by translating the T-SQL code to C# or by
using full-text indexes and searches inside
the T-SQL code.
A Reliable Alternative
SQL Server 2005, SQL Server 2000, and
many third-party products don't track object
dependencies in a reliable fashion. The
dependency checker is a much more reliable
alternative.
End of Article
Prev. page
1
[2]
next page -->
