Geek Answers Handbook

Tsql script to find tables that are not used by stored procedures, views, functions, etc.?

Is there a t-sql script to find out tables that are not used in the sql server by stored procedures, views, functions, etc. I have a database that has 100 tables, if not more before, I drop the tables, I wanted to know if there is a script that can go through every object in the database and tell me if any tables are used.

+6
source share
3 answers

If you want to use a script, here (listing the dependencies of SQL Server objects) is a very good article, like a dependency script. Using this, you can make a list of referenced tables. You have a list of tables that are in your database, so you know which one is not used.

The article uses a stored procedure.

  sp_depends
. However, it has one failure. For example, if you have a stored procedure that uses the MyTable table, and you create this procedure before creating the MyTable table, you will not see it in the dependency list. That is why you should look for a table
  syscomments
to search for dependencies. But this is also not true, because if you have a table name in a comment, you will consider it as a dependency.
+8
source

As far as I know, you cannot rely on SQL Server dependency management. Lukas noted one of many questions.

In 2005, the equivalent of the old syscomments table is sys.sql_modules.

To find all table names that are not found in the TSQL code (views, SP, functions), use this operator:

select t.name, sys.objects.name foundin, sys.objects.type_desc from sys.objects t left outer join sys.sql_modules inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id on sys.sql_modules.definition like '%' + t.name + '%' where t.type = 'U' and sys.objects.name is null order by t.name, type_desc, foundin

If you comment out a line with the IS NULL condition, you will find all occurrences of all table names in the TSQL code. (regardless of whether the table name really refers to this table)

+8
source

If you are using a management studio, you can right-click on the table and “view dependencies”.

And here is the link to the article on how to do this in tsql, which I assume is what you are looking for:

https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=277

You can always create a temporary table / var with all tables that have dependencies and compare it with all tables to see that it has no dependencies.

+4
source

More articles:


All Articles