Note : everything I’m talking about is in reference to MS SQL however I’m going to use MySQL in the examples. Why? I only have MySQL installed locally.
I was working on a job recently to drop some temp tables based on some conditions using a stored procedure and a daily SQL Agent Job. It looks like the following
- Loop over a number of system metadata tables (sys.tables, sys.schemas, sys.dm_db_partitions) for some databases I want to clean up with T-SQL to find the tables that match my criteria (basic naming and create date criteria)
- Insert the results into a temp table for each database
- Loop over the rows in the temp table and execute a drop table statement on it
- Insert each row into another table for logging (db name, table name, create date, etc)
This is fairly simple however I ran into a problem where I wanted to drop another class of tables but I wasn’t sure if these were being used or how they were used. I was fairly confident they weren’t but couldn’t be certain. There are a few ways I figured I could do this
- Post in a chat and tell people to mark them or drop themselves (fairly easy but requires effort from others)
- Use metadata for index usage to identify the last time the tables were queried (fairly precise but this may not be accurate if the system is restarted and you don’t log it)
- Create a function gradually roll out the…
