用于查找存储过程、视图、函数等未使用的表的 tsql 脚本? [英] tsql script to find tables not being used by stored procedures, views, functions, etc?

问题描述

是否有 t-sql 脚本来找出存储过程、视图、函数等未在 sql server 中使用的表.我有一个包含 100 个表的数据库，如果不是更多的话，在我走之前删除表，我想知道是否有一个脚本可以遍历数据库中的每个对象并告诉我是否有任何表正在使用中.

Is there a t-sql script to find out tables that aren't being used in sql server by stored procudures, views, functions, etc. I have a database that has 100s of tables, if not more and before I go dropping tables, I wanted to know if there was a script that could go through each object in the database and tell me if any tables are in use.

推荐答案

如果您想使用脚本，here (Listing SQL Server Object Dependencies) 是一篇关于如何编写依赖项的好文章.使用它，您可以制作被引用的表的列表.您拥有数据库中的表列表，因此您知道哪些表没有被使用.

If you want using a script, here (Listing SQL Server Object Dependencies) is a very good article how to script dependencies. Using that, you can make a list of tables being referenced. You have the list of tables that are in your database, so you know which of them are not being used.

在文章中他们使用了

sp_depends

存储过程.然而，它有一个失败.例如，如果您有一个使用表MyTable"的存储过程，并且您在创建表MyTable"之前创建了该过程，您将不会在依赖项列表中看到它.这就是为什么您应该搜索表

stored procedure. However it has one failure. For example, if you have a stored procedure that used table "MyTable" and you create the procedure before you create the table "MyTable" you won't see this on the list of dependencies. That's why you should search the table

syscomments

以查找依赖项的原因.但这也不准确，因为如果您在注释中有表的名称，您会将其视为依赖项.

to find dependencies. But this is also not accurate, because if you have the name of the table in the comment, you will treat it as a dependency.

这篇关于用于查找存储过程、视图、函数等未使用的表的 tsql 脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！