Analyzing SQL Server Dependencies – Client-side Dependencies

After cleaning up unresolved object references in an SQL Server database, we can query the database for objects not used inside the database using the statement

select name
from sys.objects o
where o.object_id not in 
    (select referenced_major_id from sys.sql_dependencies)
and o.object_id not in 
    (select referenced_id from sys.sql_expression_dependencies 
        where referenced_id is not null)
and o.type not in ('S', 'D', 'F', 'IT', 'PK', 'TR')
and not in ('fn_diagramobjects','sp_alterdiagram','sp_creatediagram',
and not like 'dev[_]%'
and o.is_ms_shipped = 0
order by o.type,

As stated, the object names are filtered according to my naming conventions, and your statements may look a bit different.

Any way, the result can be stored in a text file (unreferenced.txt), and each line contains a single object name.

Next, we create a batch file find-unreferenced.cmd with the following statements to check for object usage in an ASP.Net application:

@echo off
for /f %%i in (unreferenced.txt) do ( 
    echo %%i
    grep -d -l -i %%i *.cs | findstr /i /v "generated"
    grep -d -l -i %%i *.aspx 
    grep -d -i -l %%i *.ascx 
    grep -d -i -l %%i *.master 

Adjust the command line switches (-d: recursive, -l: filenames only) to your version of the grep tool, or replace it to use findstr instead. The command “| findstr /i /v “generated” ” is used to filter out code generated from the database.

As the batch file is executed, objects which are referenced by client code show up as

Object Name

Objects not referenced by client code are only displayed by name, without subsequent hits from grep or findstr.

1 thought on “Analyzing SQL Server Dependencies – Client-side Dependencies

  1. Pingback: Series: Analyzing SQL Server Dependencies | devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.