Strategy for finding and removing redundant objects from a SQL Server 2000 database

Question

Strategy for finding and removing redundant objects from a SQL Server 2000 database

jatighe

SSChasing Mays

Points: 638
More actions
October 6, 2004 at 7:44 am

#163431

I am looking for some advice on the best way of finding and removing redundant objects from one of my sql server 2000 databases. What would be the best way of going about this task?
Regards
Justin

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

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

DDRiviello SSC Enthusiast Points: 150 More actions · Answer 1

Justin,

The answer would depend on what you mean by "redundant objects". Do you mean tables with duplicate rows, or more than one index with the same columns on the same table - or did you mean the same object in more than one database on the same server? Two objects can't have the same name in the same database. There are queries that can help with any of these.

Danette

jatighe SSChasing Mays Points: 638 More actions · Answer 2

Thanks Danette for you reply, I actually mean't objects that were no longer required in the database, such as Views, store procedures even tables that are no longer used, rather then data issues.

Is there a way of identifying objects that have not been used for a period of time?

Regards

Justin

DDRiviello SSC Enthusiast Points: 150 More actions · Answer 3

That is much tougher! I am always wary of making anyassumptions about that something is never used, because things could be used very infrequently, but be very necessary. Is your database an online transaction processing (OLTP) system or more of reporting database? You could set up Profiler to look for a specific stored procedure for a while, but that will add overhead to your system and slow performance.

However, if you want to find stored procedures and views that depend on a table you think is not used, or you know you have dropped, you could do this query in the database and any other database that could possible reference it:

select o.name "ObjectName", c.text from dbo.sysobjects o, dbo.syscomments c where o.id = c.id and c.text like '%tablename%'

I hope that helps.

Danette

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Profiler is really the only good way of doing this. Be sure that if you remove stuff, you archive it so that if 6 months from now you find out that it's needed, you can restore it.

Be wary of this, thre might be objects used for one time a year, like end of year processing, and you might not be aware. I like cleaning things up, but be very careful and ocnservative.