找到应用程序不使用的所有列 [英] Locate all columns that the application don’t use

问题描述

我有一个有趣的任务。我们正在使用一堆填充了我们不再使用的数据的表。问题是我没有过去使用我们的SQL Server表的应用程序的文档，所以我必须手动查找未使用的列数据。是否有一些工具可以帮我完成这项工作？

I have interesting task. We are using a bunch of tables filled with data we don’t use anymore. Problem is I don’t have documentation for applications used in the past with our SQL Server tables so I must manually locate unused column data. Is there some tool out there to do this job for me?

推荐答案

我认为，如果有足够的时间，我可以证明一般情况下这是理论上不可能。但我可以给你一个想法：

http://en.wikipedia.org/wiki/Halting_problem [ ^ ]。



你得到的照片？您的应用程序中可能有一些死代码，它使用了一些列但实际上从未被调用过。理论上不可能证明它被调用（或不被调用），这可以证明是停止问题的结果。实际上，这更难。



原则上，你可以做更多较弱的陈述：你可以删除名称中没有硬编码的列。换句话说，它只能帮助一些草率的产品，仍然不能100％保证功能不被破坏。而且我不希望任何人会开发这样的工具。

-SA

I think that, given enough time, I could proof that in general case this is theoretically impossible. But I can give you the idea:
http://en.wikipedia.org/wiki/Halting_problem[^].

Are you getting the picture? You may have some "dead code" in your application, which uses some columns but is never actually called. It is theoretically impossible to proof that it is called (or not), which can be proven as a consequence of halting problem. Practically, this is even harder.

In principle, you could do much weaker statements: you could eliminate the columns with names which are not hard-coded in the product. In other words, it can help only some sloppy products and still cannot 100% guarantee that the functionality is not broken. And I would not expect that anyone would be developing a tool like that.

—SA

我同意这几乎是不可能的。如果您在应用程序中使用select *之类的查询，那么您只能去删除数据....

首先，您需要检查您的应用程序代码。这可能很耗时，但它是一个很好的起点。查找每列的所有引用...

您可以对要清理的表进行性能分析。让它运行几天而不是检查日志。



我知道的唯一工具是 http://www.red-gate.com/products/sql-development/sql-dependency-tracker/ [ ^ ]

它可以找到所有孤儿对象。

I agree it is almost impossible.If you have queries like select * used in your application than you cant just go and delete data ....
First you need to examine your application code. This can be time consuming, but it is a good place to start. Finding all references for each column...
You can do a profiling for tables you want to clean. Just let it run few days and than examine logs.

The only tool i know for this is http://www.red-gate.com/products/sql-development/sql-dependency-tracker/[^]
It can find all "orphan" objects.

这篇关于找到应用程序不使用的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！