I am using a SQL Server 2000 database that likely includes a couple of dozen tables which are no more utilized. Let me obvious the data that people no more have to be maintaining, but I am unsure how you can identify which tables to get rid of.
The database is shared by a number of different programs, and so i can not be 100% certain that looking at these can give us a complete listing of the objects which are used.
What Let me do, whether it's possible, is to buy a listing of tables that weren't utilized whatsoever for many time period. No reads, no creates. How must i approach this?
MSSQL2000 will not provide you with that type of information. But a method for you to identify what tables Are utilized (after which deduce which of them aren't) is by using the SQL Profiler, in order to save all of the queries that visit a certain database. Configure the profiler to record the outcomes to a different table, after which look into the queries saved there to locate all of the tables (and sights, sps, etc) which are utilized by your programs.
One other way I believe you may see if there's any "creates" would be to give a new timestamp column to each table, along with a trigger that updates that column each time there's an update or perhaps an place. But bear in mind when your applications do queries from the type
select * from ...
they will get a brand new column which could potentially cause you some problems.
Another suggestion for monitoring tables which have been written to is by using Red Gate SQL Log Rescue (free). It dives in to the log from the database and will highlight all card inserts, updates and removes. Their email list is fully searchable, too.
It does not meet your criteria for researching reads in to the database, however i think the SQL Profiler technique will enable you to get a reasonable idea so far as that goes.
For those who have lastupdate posts you should check for that creates, there's really no good way to look for reads. You can run profiler, save the trace to some table and sign in there
Things I usually do is relabel the table by prefixing it by having an underscrore, when individuals begin to scream I simply relabel it back
If by not used, you mean the application doesn't have more references towards the tables under consideration and you're simply using dynamic sql, you can perform a look for the table names inside your application, when they don't exist blow them.
I have also outputted all sprocs, functions, etc. to some text file and done searching for that table names. Otherwise found, or present in methods that will have to be erased too, blow them.
Just remove them and find out if any user gripes about this )
It appears like while using Profiler will work. Once I have turn it on for some time, I ought to have a very good listing of used tables. Anybody who does not use their tables every single day can most likely wait to allow them to be restored from backup. Thanks, folks.
Most likely past too far to assist mogrify, however for anybody carrying out a search I'd look for all objects by using this object during my code, then in SQL Server by running this :
choose distinct '[' + object_title(id) + ']'
where text like '%MY_TABLE_Title%'