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.