find all tables not referenced in stored procedures

I have sql server database with numerous tables, some no longer used so I want to remove them. All database interactivity is via stored procedure to these tables.

Is there a database sql script that I can use that will list all tables not r开发者_运维问答eferenced in any of the stored procedures in the database?

---

If SQL Server 2008 then the dependencies 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)  )

---

You can't do this if you use any dynamic T-SQL. Dynamic T-SQL won't show up in any investigation of object dependencies.

Instead, you can use the DMV sys.dm_db_index_usage_stats to find what objects haven't been referenced by any queries. Here's a query I did on SQLServerPedia for that:

http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

The query is designed for performance tuning indexes, so you'll need to tweak a few lines. 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'll need to sift through - some of your objects may be heaps, some may have clustered indexes, etc. I'll leave this as a wiki so someone more ambitious than me can edit it to build a deduped list. :-D

---

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

And this article(listed from above discussion) http://www.mssqltips.com/tip.asp?tip=1294 discusses about SQL object dependencies.

---

Perhaps something along these lines:

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

---

The first query lists table with the stored proc name that uses it. The second query lists 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

---

Here's one you might 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's a reference I've used in the past:

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

---

If performace isnt to much of a problem you could 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, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within your database

---

Most of this code doesn't work if there are schemas other than "dbo", or if the user's default schema is not "dbo". Here's an update to one of the scripts to fix that:

select t.Table_Schema + '.' + 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_schema + '.' + r.ROUTINE_NAME)) like '%'+t.TABLE_NAME+'%'
) order by t.TABLE_NAME