How to check a database in SQL to make sure that all of its tables are in use?

Question

I was assigned to see if all of the current tables in a database are used and if not to drop them. These are the steps I have taken so far:

1. Searched tables names in the program that uses that database to see if a query has been made in the program based on those tables names.

2. Investigated if a table primary key has been used in any other places such as view or table (Connectivity with other used tables). I used:

   SELECT 
       t.name AS table_name, 
       SCHEMA_NAME(schema_id) AS schema_name, 
       c.name AS column_name 
   FROM 
       sys.tables AS t 
   INNER JOIN 
       sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
   WHERE 
       c.name LIKE 'DeflectionId' -- write the column you search here 
   ORDER BY 
       schema_name, table_name; 
   

3. Searched inside all of the stored procedure texts to see if a table name has been used inside them:

   SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
   FROM  
       sys.sql_modules m
   INNER JOIN
       sys.objects o ON m.object_id = o.object_id
   WHERE 
       m.definition LIKE '%\[Test_Results_LU\]%' ESCAPE '\';
   

   or

   SELECT name
   FROM sys.procedures
   WHERE Object_definition(object_id) LIKE '%Test_Results_LU%'
   

   (from this link: Search text in stored procedure in SQL Server )

4. Used Object Explorer view to see if a table with the similar/same name and size exists in the database.

Do you think there are other ways that I can use to investigate it better?

Are these steps efficient at all? How would you do it?

Answer 1

Those are all reasonable things to check. One more thing to do would be to turn on profiling or auditing, depending on your SQL server version, and actually monitor for the tables being used for a reasonable time period. You may not be able to do that with a production system, and it's still not 100% guaranteed - what if there's an important table that's only queried once a year?

https://dba.stackexchange.com/questions/40960/logging-queries-and-other-t-sql https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/view-a-sql-server-audit-log?view=sql-server-2017

One other suggestion before dropping the tables is to explicitly remove access to them (either with DENY/REVOKE or rename them to table-name_purge) for a week or two and see if anyone complains. If they don't, then it's probably safe to make a backup and then drop them.

Answer 2

A couple of other places to check. Both of these rely on data that is

• cached automatically by the system
• not persisted between restarts
• can be dropped at any time.

so absence from these results does not prove that the table is not used but you may find evidence that a table definitely is in use.

SELECT [Schema] = OBJECT_SCHEMA_NAME(object_id), 
       [ObjectName] = OBJECT_NAME(object_id),
       *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()

And in the plan cache

USE YourDB

DROP TABLE IF EXISTS #cached_plans, #plans, #results

DECLARE @dbname nvarchar(300) = QUOTENAME(DB_NAME());


SELECT dm_exec_query_stats.creation_time,                               
       dm_exec_query_stats.last_execution_time,                             
       dm_exec_query_stats.execution_count,                             
       dm_exec_query_stats.sql_handle,                              
       dm_exec_query_stats.plan_handle                              
INTO   #cached_plans                                
FROM   sys.dm_exec_query_stats;                             

WITH distinctph                             
     AS (SELECT DISTINCT plan_handle                                
         FROM   #cached_plans)                              
SELECT query_plan,                              
       plan_handle                              
INTO   #plans                               
FROM   distinctph                               
       CROSS APPLY sys.dm_exec_query_plan(plan_handle);                             

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')                              
SELECT      cp.*,                               
            st.text,
            [Database] = n.value('@Database', 'nvarchar(300)'),
            [Schema] = n.value('@Schema', 'nvarchar(300)'),
            [Table] = n.value('@Table', 'nvarchar(300)')                            
INTO        #results                                
FROM        #cached_plans cp                                
JOIN        #plans p                                
ON          cp.plan_handle = p.plan_handle                              
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
CROSS APPLY query_plan.nodes('//Object[@Database = sql:variable("@dbname") and @Schema != "[sys]"]') qn(n);

SELECT *
FROM #results