tsql script to find tables not being used by stored procedures, views, functions, etc?

Question

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.

Answer 1

If you're using management studio, you can right-click on a table and 'view dependencies.'

And here is a link to an article on how to do this in tsql, which I'm guessing is what you're looking for:

http://blogs.techrepublic.com.com/datacenter/?p=277

You could always build up a temp table / var with all the tables that have dependencies, and compare it against all tables to see what doesn't have any dependencies.

Answer 2

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.

In the article they use

sp_depends

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.

Answer 3

As far as I know you cannot really rely on SQL Server's dependency management. Lukasz pointed out one of the many issues.

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

To find all table names which do not occur in TSQL code (views, SPs, functions), use this statement:

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 the line with the IS NULL condition, you find all occurrences of all table names in TSQL code. (no matter if the table name really refers to that table)