Finding orphaned stored procedures and user-defined functions in SQL Server

I’m currently working on a group of ASP.NET 2.0 websites deployed across about thirty countries. The local flagship site runs on an upgraded version of the original code, and I’m now in the process of bringing all the other sites onto the new improved version.

Over time, new features have been introduced to the site, and old ones removed. Consequently the SQL Server database now contains many redundant tables that aren’t used. So, before cascading out the current schema to the other countries, it’s time for a clean up.

I managed to identify about 60 tables that aren’t used by the application and can safely can be dropped or archived. However, I’m now left with hundreds of stored procedures (SPs) and user-defined functions (UDFs) that were associated with these tables, which can also be removed.

The problem was how to find these orphaned objects. My first approach was a small .NET console application which uses SQL Server Management Objects (SMO). It loops through all SPs and UDFs and finds any that have no dependencies.

public List<string> FindOrphans()

{

    Server server = new Server(".");

    Database db = server.Databases["MyDatabase"];

    List<string> orphans = new List<string>();

    // get list of SPs

    UrnCollection urns = new UrnCollection();

    foreach (StoredProcedure sp in db.StoredProcedures)

    {

        // exclude these objects

        if (sp.IsSystemObject) continue;

        if (sp.Name.StartsWith("aspnet_")) continue;

        urns.Add(sp.Urn);

    }

    // get dependencies

    DependencyWalker dw = new DependencyWalker(server);

    DependencyTree tree = dw.DiscoverDependencies(urns, true);

    // find all objects without any dependencies

    DependencyTreeNode node = tree.FirstChild;

    do {

        if (!node.HasChildNodes)

        {

            string name = new Urn(node.Urn).GetAttribute("Name");

            orphans.Add(name);

        }

        node = node.NextSibling;

    } while (node != null);

    return orphans;

}

This works fine, and helped satisfy my current obsession with SMO. But it’s a bit awkward, and not easily portable or modifiable, to have this pure database operation wrapped up in an executable. So I looked into doing the same thing with just a TSQL query.

-- Find all SPs and UDFs have no dependencies

select

    object_name(obj.[object_id]) as [orphaned_object_name],

    obj.type_desc as [object_type],

    'DROP ' +

    case obj.type_desc

        when 'SQL_STORED_PROCEDURE' then 'PROCEDURE'

        else 'FUNCTION'

    end

    + ' [' + object_name(obj.[object_id]) + ']'

from

    sys.objects obj

    left join (select distinct [object_id] from sys.sql_dependencies) dep

        on obj.object_id = dep.object_id

where

    type_desc in ('SQL_STORED_PROCEDURE','SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION')

    and object_name(obj.[object_id]) not like 'aspnet_%'

    and dep.object_id is null

order by

    obj.type_desc, object_name(obj.[object_id])

The query works by checking for dependencies in the catalog view sys.sql_dependencies. This, I think, is a neater solution. I also included an auto-generated column that writes the SQL drop the SP or UDF, which I copied and executed.

Now, if only I could find a quick way to check for dependencies between my application’s data access layer and the database…