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 functionschema_name()
can be used to translate theschema_id
column to that actual name.sys.procedures
– contains all procedures. Functionschema_name()
can be used here also.sys.sql_modules
– contains the actual procedure code in the columndefinition
. This table joins tosys.procedures
using the columnobject_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.
1条答案
按热度按时间k2fxgqgv1#
A couple of observations:
[Final].[NameOfTabletImSearching]
? Your query would miss that entirely.For these cases, I'd check sys.sql_expression_dependencies. Like so:
Note: there's no need to loop through a list of tables here. Generally speaking, iterating a list in SQL is a "code smell" that should incite pause.
I leave it as an exercise to adapt the query above to accommodate synonyms as well. You can either add it as a predicate to that query (for an all-in-one query) or run it separately.
Lastly, checking that there are no references in stored procedures/functions is necessary but not sufficient if the goal is to eventually get rid of these tables. That is, there could be processes accessing the tables directly. I've used SQL Audit in similar cases for tables that I've suspected of being unused over a period of time prior to renaming and ultimately dropping them.