I was assigned the task of going through the tables in our database and writing a query to figure out which tables are not used anymore by any of our procedures.

Here are some starting points I have to try and figure out how to write the query.

System tables to query:

• sys.tables – contains all tables. The function schema_name() can be used to translate the schema_id column to that actual name.
• sys.procedures – contains all procedures. Function schema_name() can be used here also.
• sys.sql_modules – contains the actual procedure code in the column definition . This table joins to sys.procedures using the column object_id . You can query the definition column for the existence of tables using the like predicate and wildcards for example: where definition not like '%<schema_name.table_name>%'

Thanks!

select 
    schema_name(schema_id), name
from 
    sys.tables
where 
    schema_name(schema_id) in ('Final', 'Stage', 'Utils', 'TRX', 'Reporting')
order by 
    schema_name(schema_id)

Here is a simple query I wrote that gets me a list of all our tables. I've put this list into a temp table and my goal is to loop through that temp table i create passing in the current table to the query of the procedures/ sql_module tables.

From that list I can individually take each table and query it against the procedures/sql_modules to see if it is used in any of our procedures.

select
    schema_name(schema_id), name
from 
    sys.procedures p
inner join 
    sys.sql_modules sm on p.object_id = sm.object_id
where 
    sm.definition like '%Final.NameOfTabletImSearching%'

This query will return a list of procedures that use the table I'm searching, or will return blank if no procedures return the table, but what I am trying to do is write a query that can give me a list of tables that are not used at all using the temp table I passed in.