Post reply

Identifying Obsoleted Database Objects

  • October 9, 2008 at 6:42 am

    #96753

    I am working on an ASP.NET (2.0) VB application with a SQL Server 2005 database. I use stored procedures exclusively. I am just finishing up a major revision which entails a number of web pages being rewritten or just plain old discarded. Is there a way to identify any stored procedure no longer referenced?

  • October 9, 2008 at 7:01 am

    #882860

    Only by searching through your code. SQL Server doesn't know what isn't being called from other applications.

    Once you get running, you can run Profiler and look for objects that are being referenced, use process of elimination to find those that aren't.

  • October 9, 2008 at 7:05 am

    #882865

    I think you'd have to do this with a quick and dirty .net applacation, since it's obvious you can't on the server side:

    i would think that you'd have to recursively search the source code of the new .NET projects; for each name of the stored procedures in the database, you have to see if it appears in the source code, and infer if the name of a proc is not found, it might be deprecated.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 9, 2008 at 8:14 am

    #882910

    actually, since I have been soooo disciplined - defining StringBuilders and initializing them as EXECUTE sp I can do a global search for EXECUTE

    and find the stored procedures that way

    any that aren't used (or are only in excluded pages) can be DROP'ped

    :hehe:

    thanks guys (or women)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply