识别 SQL Server 2000 中未使用表的策略？
我正在使用一个 SQL Server 2000 数据库，该数据库可能有几十个不再被访问的表。 我想清除不再需要维护的数据，但我不确定如何确定要删除哪些表。
该数据库由多个不同的应用程序共享，因此我不能 100% 确信查看这些应用程序会给我所使用的对象的完整列表。
如果可能的话，我想做的是获取一段时间内根本没有被访问过的表的列表。 没有读，没有写。 我应该如何处理这个问题？
I'm working with a SQL Server 2000 database that likely has a few dozen tables that are no longer accessed. I'd like to clear out the data that we no longer need to be maintaining, but I'm not sure how to identify which tables to remove.
The database is shared by several different applications, so I can't be 100% confident that reviewing these will give me a complete list of the objects that are used.
What I'd like to do, if it's possible, is to get a list of tables that haven't been accessed at all for some period of time. No reads, no writes. How should I approach this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您有 LastUpdate 列，则可以检查写入情况，但实际上没有简单的方法来检查读取情况。 您可以运行探查器，将跟踪保存到表中并在那里检查
我通常做的是通过在表前加上下划线来重命名表，当人们开始尖叫时我只是将其重命名回来
If you have lastupdate columns you can check for the writes, there is really no easy way to check for reads. You could run profiler, save the trace to a table and check in there
What I usually do is rename the table by prefixing it with an underscrore, when people start to scream I just rename it back
如果未使用，则意味着您的应用程序不再引用相关表，并且您正在使用动态 sql，您可以在应用程序中搜索表名，如果它们不存在，则将其删除。
我还将所有存储过程、函数等输出到文本文件并搜索表名称。 如果没有找到，或者在需要删除的程序中找到，则将其删除。
If by not used, you mean your application has no more references to the tables in question and you are using dynamic sql, you could do a search for the table names in your app, if they don't exist blow them away.
I've also outputted all sprocs, functions, etc. to a text file and done a search for the table names. If not found, or found in procedures that will need to be deleted too, blow them away.
看来使用 Profiler 会起作用。 一旦我让它运行一段时间，我应该有一个很好的已用表列表。 任何不每天使用其表的人都可以等待它们从备份中恢复。 谢谢各位。
It looks like using the Profiler is going to work. Once I've let it run for a while, I should have a good list of used tables. Anyone who doesn't use their tables every day can probably wait for them to be restored from backup. Thanks, folks.
可能为时已晚，无法帮助 mogrify，但对于任何进行搜索的人来说； 我将在代码中搜索使用此对象的所有对象，然后在 SQL Server 中运行以下命令：
select unique '[' + object_name(id) + ']'
来自系统评论
其中文本如“%MY_TABLE_NAME%”
Probably too late to help mogrify, but for anybody doing a search; I would search for all objects using this object in my code, then in SQL Server by running this :
select distinct '[' + object_name(id) + ']'
from syscomments
where text like '%MY_TABLE_NAME%'
跟踪已写入表的另一个建议是使用 Red Gate SQL Log Rescue （免费）。 该工具深入数据库日志，并向您显示所有插入、更新和删除。 该列表也是完全可搜索的。
它不符合您研究数据库读取的标准，但我认为 SQL Profiler 技术将使您对此有一个合理的想法。
Another suggestion for tracking tables that have been written to is to use Red Gate SQL Log Rescue (free). This tool dives into the log of the database and will show you all inserts, updates and deletes. The list is fully searchable, too.
It doesn't meet your criteria for researching reads into the database, but I think the SQL Profiler technique will get you a fair idea as far as that goes.
MSSQL2000不会给你那种信息。 但是，您可以识别使用了哪些表（然后推断出哪些表没有使用）的一种方法是使用 SQL Profiler，以保存进入某个数据库的所有查询。 配置探查器以将结果记录到新表中，然后检查其中保存的查询以查找应用程序使用的所有表（以及视图、sps 等）。
我认为您可以检查是否有任何“写入”的另一种方法是向每个表添加一个新的时间戳列，以及每次更新或插入时更新该列的触发器。 但请记住，如果您的应用程序执行该类型的查询
，那么它们将收到一个新列，这可能会给您带来一些问题。
MSSQL2000 won't give you that kind of information. But a way you can identify what tables ARE used (and then deduce which ones are not) is to use the SQL Profiler, to save all the queries that go to a certain database. Configure the profiler to record the results to a new table, and then check the queries saved there to find all the tables (and views, sps, etc) that are used by your applications.
Another way I think you might check if there's any "writes" is to add a new timestamp column to every table, and a trigger that updates that column every time there's an update or an insert. But keep in mind that if your apps do queries of the type
then they will receive a new column and that might cause you some problems.