Find all tables not specified in stored procedures

Question

I have a sql server database with numerous tables, some of them are no longer in use, so I want to delete them. All database interactivity is done through a stored procedure for these tables.

Is there a sql script database that I can use that will display all tables not specified in any of the stored procedures in the database?

Answer 1

You cannot do this if you are using dynamic T-SQL. Dynamic T-SQL will not be displayed in any exploration of object dependencies.

Instead, you can use DMV sys.dm_db_index_usage_stats to find which objects were not referenced by any queries. Here is the query I made for SQLServerPedia for this:

http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

The query is for performance indices, so you need to tune a few rows. Here's the modified query:

SELECT o.name , indexname=i.name , i.index_id , reads=user_seeks + user_scans + user_lookups , writes = user_updates , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) , CASE WHEN s.user_updates < 1 THEN 100 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates END AS reads_per_write , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement' FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id INNER JOIN sys.objects o on s.object_id = o.object_id INNER JOIN sys.schemas c on o.schema_id = c.schema_id WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND s.database_id = DB_ID() ORDER BY reads 

Keep in mind that this catches all indexes, and you need to sift through - some of your objects may be heaps, some may have clustered indexes, etc. I will leave it as a wiki, so that someone is more ambitious than I can edit it to build an output list.: - D

Answer 2

If SQL Server 2008, then the dependency information is now reliable.

 SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t WHERE is_ms_shipped = 0 AND NOT EXISTS (SELECT * FROM sys.sql_expression_dependencies d WHERE d.referenced_entity_name = t.name AND (( is_ambiguous = 1 or is_caller_dependent=1) OR d.referenced_id = t.object_id) ) 

Answer 3

Check out this discussion of the tsql script to find tables that are not used by stored procedures, views, functions, etc.?

And this article (listed above) http://www.mssqltips.com/tip.asp?tip=1294 discusses the dependencies of SQL objects.

Answer 4

Maybe something like this:

 select t.table_name from INFORMATION_SCHEMA.TABLES t where not exists ( select 1 from INFORMATION_SCHEMA.ROUTINES r where object_definition(object_id(r.ROUTINE_NAME)) like '%'+t.TABLE_NAME+'%' ) order by t.TABLE_NAME 

Answer 5

The first query displays a table with the stored name proc that uses it. The second query displays a table with the number of stored procs using it.

 -- list all tables / sprocs select t.name [Table], p.name [StoredProc] from sys.tables t left join sys.procedures p on (OBJECT_DEFINITION(p.object_id)) like '%' + t.name + '%' where t.type = 'U' order by t.name, p.name -- count stored procs using table select t.name [Table], count(p.name) [Count] from sys.tables t left join sys.procedures p on (OBJECT_DEFINITION(p.object_id)) like '%' + t.name + '%' where t.type = 'U' group by t.name order by t.name 

Answer 6

Here you can try:

 select name from sys.tables t left join sys.sql_dependencies d on t.object_id = d.referenced_major_id where d.referenced_major_id is null 

Otherwise, here is the link I used in the past:

http://www.mssqltips.com/tip.asp?tip=1294

Answer 7

If performace is not a big problem, you can try the following.

 Select Distinct Object_Name(ID) From syscomments Where ID Not In (Select ID From syscomments Where Text Like '%<TableName>%') 

This will check each view, usually the default value, trigger, CHECK constraint, default constraint and stored procedure in your database.