obsolete objects

[dmando84]

Hello,



I need to find all of the SQL Server objects, (tables, procs, functions, etc.) in my database, that my application is no longer using so they can be removed from the system. Does anyone know how to accomplish this?



Thank you,



Dave

 

[ca8msm]

How would SQL Server know if your application is "using" them? Do you mean you want to look at the last time an SP was executed and work it out yourself from that date?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

see here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3227652&SiteID=1,

I tried to reply in the msdn forum but it wouldn't let me



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

I'll paste it here also

just because your app doesn't use a function or a view doesn't mean that the function or view is not used inside a proc that is used by your app



generate a list of all the objects used by your app, run sp_depends to check for other objects (not fail safe because of deferred name resolution when objects are created)



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[dmando84]

Thank you for your respone...

I need to basically find all of the objects that my companies web site is not using. We are upgrading from SQL 2K to 2K5. So , if a table is not being used at all by the web site, then I need to drop it. Same goes for procs, functions, trigger, etc. I hope this is a clearer explanation.

Thanks,

Dave

 

[ca8msm]

No, it's still not that clear to me. What happens if a stored procedure exists, nothing depends on it but your application hasn't executed it for say, 6 months. How would you determine that this means that your application "doesn't use it"?

You need to explain what logic defines "not used".


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[dmando84]

That's the problem I am not really sure. I will have to ask around...

Thanks

 

[nice95gle]

My last assignment was to build a tool just for that purpose (a tool to find objects in your database(s) that's no longer used). It works on 1 DB or across multiple DB's. So if an object in DB1 is referring an object in DB1 it will flag it, also if an object in DB1 is referring an object in DB2 it will flag it.

I ran out of time before I built the link to DTS/SSIS so if your object is used by a package it can't flag it.

I will look for it and post up the code tomorrow in the FAQ's.

But as stated above. If an outside object is using a table, proc or whatever it can't tell.


Well Done is better than well said
- Ben Franklin

 

[SQLSister]

This is yet another argument for never using dynamic SQl if you have a choice. If you are creating queries on the fly either through the user app or in dynamic SQl in a stored proc, how would you know whether they are ever accessing those tables, etc?

To simplify the task, I would first identify which objects I know are used. Then you have fewer to research. You could run profiler for a bit to identify the most common ones being used. Once you have a smaller list, the task is not so daunting.

We have an sp that searches through all the sps in a database for a named object and tells us where it is refernced. Very handy for something like this, but it still wouldn't find things used in steps of jobs, DTS packages etc. (search google for sp_grep for various versions of this)

You might also search your application source code for references to objects.

Once you have the list of ones to research, pay particular attention to any that might indicate a quarterly or annual report. These may be used so infrequently that your profiler list didn't include them. Check with the creator of the proc if possible to see if they are still used or look for a job that runs them.

Finally, once you have a potential list of objects that you think can be deleted, send it around to the entire development staff. Likely you will have some that someone knows the use for or knows positively that it is no longer in use because he or she wrote the replacement code.

Before deleting any objects, we normally re-name them with an _ at the beginining. Then it is easy to get them back if you start getting errors. In a big effort like this (rather than doing this at the time the object stops being used), I would create a copy of the database as is before dropping any objects. Again this will make it easier to get back any that were dropped and you found out they were being used.

After dropping all the objects on dev, do an all-out test of the system. Try everything in the user interface, run all reports especially those not run often (annual or quarterly ones), run all jobs, run all imports. Far better to find something missing on dev than in prod.

"NOTHING is more important in a database than integrity." ESquared