如何在SQL Server 2008R2中查找未使用的列

I want to remove unused fields to improve clarity and possibly to improve performance. But first I have to find out the unused fields in SQL Server 2008R2. Can any one help me on this?

我想删除未使用的字段以提高清晰度,并可能提高性能。但首先我必须找出SQL Server 2008R2中未使用的字段。谁可以帮我这个事?

1 个解决方案

#1

This task isn't simple at all, but you can start with the following:

这项任务并不简单,但您可以从以下内容开始:

Query the sys.sql_expression_dependencies system view.

查询sys.sql_expression_dependencies系统视图。

Query the sys.objects system view, filtering by OBJECT_DEFINITION(O.object_id) LIKE '%YourColumnName%'. This will list all objects that have that column in it's code, will also list them if they are commented o partially written (LIKE).

查询sys.objects系统视图,按OBJECT_DEFINITION(O.object_id)LIKE'%YourColumnName%'过滤。这将列出在其代码中包含该列的所有对象,如果它们被部分写入(LIKE),则也会列出它们。

Use the SQL Profiler to capture daily traffic over a decent amount of time depending on your system and review the requests for containing your columns.

使用SQL事件探查器在相当长的时间内捕获每日流量,具体取决于您的系统并查看包含列的请求。

Unfortunately, if your column names are common words (like person, date, id, etc), the 2nd and 3rd methods will yield a lot of code to review.

不幸的是,如果您的列名是常用词(如person,date,id等),则第2和第3种方法将产生大量要审查的代码。

Also keep in mind that if you execute dynamic SQL against those tables, it might be very hard to catch it.

还要记住,如果对这些表执行动态SQL,可能很难捕获它。

#1