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 referenced 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) ) 
referenced_database_name in sys.sql_expression_dependencies that can be looked at. I haven't taken account of that at all in my answer and additionally my answer would need tweaking if the OP has any columns named exactly the same as one of their tables. I've left that as an exercise for the OP!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:
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