如何在SQL Server中查找未使用的表

Is there a way of finding out when the data was last entered into a table? I'm trying to find obsolete tables within my database and would like to know if there is a simple script(s) that I can run?

有没有办法找出数据最后输入表格的时间?我试图在我的数据库中找到过时的表,并想知道是否有一个我可以运行的简单脚本?

3 个解决方案

#1

You could try check the results of querying the sys.dm_db_index_usage_stats Dynamic Management View like this:

您可以尝试检查查询sys.dm_db_index_usage_stats动态管理视图的结果,如下所示:

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID() 
    AND [object_id] = OBJECT_ID('TableName')

This will return things like the last_user_seek, scan and update dates on the indexes on the table.

这将返回表上索引的last_user_seek,扫描和更新日期等内容。

Howvever, beware as the stats for the dynamic management view are reset when the server is restarted. The longer the server has been running, the more confidence you can have if the records show no activity.

但是,请注意,重新启动服务器时会重置动态管理视图的统计信息。服务器运行的时间越长,如果记录显示没有活动,您就越有信心。

I personally would also be checking all the source code to check for references to the table in question, and searching all sprocs/UDFs for references too (you can use SQL Search from Red Gate to do this - it's free)

我个人也会检查所有源代码以检查对相关表的引用,并搜索所有sprocs / UDF以获取引用(您可以使用Red Gate的SQL搜索来执行此操作 - 它是免费的)

#2

If anyone is looking for all unused tables in a database (more the title's request than the question body), this guy had a good query to bring in all unaltered tables in a database. In this case, "unaltered" is any table without an entry in sys.dm_db_index_usage_stats (again, as with AdaTheDev's answer, only since the last sql server reboot).

如果有人在数据库中查找所有未使用的表(更多的是标题的请求而不是问题正文),那么这个人有一个很好的查询来引入数据库中所有未更改的表。在这种情况下,“未更改”是sys.dm_db_index_usage_stats中没有条目的任何表(再次,与AdaTheDev的答案一样,仅在最后一次sql server重新启动后)。

SELECT OBJECTNAME = Object_name(I.object_id), 
       INDEXNAME = I.name, 
       I.index_id 
FROM   sys.indexes AS I 
       INNER JOIN sys.objects AS O 
               ON I.object_id = O.object_id 
WHERE  Objectproperty(O.object_id, 'IsUserTable') = 1 
       AND I.index_id NOT IN (SELECT S.index_id 
                              FROM   sys.dm_db_index_usage_stats AS S 
                              WHERE  S.object_id = I.object_id 
                                     AND I.index_id = S.index_id 
                                     AND database_id = Db_id(Db_name())) 
ORDER  BY objectname, 
          I.index_id, 
          indexname ASC

#3

If this is important to your application(s) and/or company, and the tables were designed correctly, then each table should have a column called something like 'LastModifiedTime'. You can query this table to determine which tables are obsolete.

如果这对您的应用程序和/或公司很重要,并且表格设计正确,那么每个表都应该有一个名为“LastModifiedTime”的列。您可以查询此表以确定哪些表已过时。

#1