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 o.name not in ('fn_diagramobjects','sp_alterdiagram','sp_creatediagram',
    'sp_dropdiagram','sp_helpdiagramdefinition','sp_helpdiagrams',
    'sp_renamediagram','sp_upgraddiagrams')
and o.name not like 'dev[_]%'
and o.is_ms_shipped = 0
order by o.type, o.name

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
filename1.cs:
filename2.cs:
filename3.aspx:

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

Advertisements

One Response to Analyzing SQL Server Dependencies – Client-side Dependencies

  1. […] Analyzing SQL Server Dependencies – Client-side Dependencies […]

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: