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…