Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
davidshq
Posting Yak Master
119 Posts |
 Posted - 2006-11-09 : 15:53:53
|
| I have a large database in which I believe there are some legacy tables. Is there any easy way to find out from SQL Server on what date tables were last used? This would be a significant aid in figuring out which tables are in use and which aren't. Thanks.David.- http://www.gamesecretary.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-10 : 04:21:29
|
| No easy way I know of to do this. You could log queries in SQL Profiler, and some of those will include table names, but if you are using Stored Procedures, and importantly if SProcs conditionally call other SProcs, then its very hard to work out.We do this, mostly, using a fully automated QA process, and we rename tables with a suffix of "_NU_yyyymmdd" and see if the QA process fails.You could do this on a live database too - which is known around these parts as a "scream test" - but the users may not appreciate it!I was wondering whether you could rename the tables and replace them with a VIEW that just did a SELECT * from the renamed-table, and also something else that would be logged, but I'm struggling to think what. Perhaps a Function that took the Table name as a parameter and used OPENQUERY to store the table's name in a "actually was used" table?Kristen |
 |
|
|
|
|
|
Find Unused Tables?