This was asked on twitter the other day and I emailed the person the solution to this. The solution uses dynamic management views and it is not perfect because of a couple of reasons.

1) The dynamic management views don’t keep this information forever, restart the server and your data is gone

2) If your table name is in a comment it will be picked up by this query

3) If the table name is part of another object it will also be picked up, for example if you have a table name customer and a view name customers then it will return a row if customers was part of the query but you are lloking for the customer table

So let’s look at some code

First create the following stored procedure

create proc prTestProc
as
select * from master..spt_values where type = 'P'
go

Now run this query 5 times

select * from master..spt_values where type = 'P'

Run this query 6 times

select count(*) from master..spt_values where type = 'P'

Run this query 7 times

select count(*) from master..spt_values

Run this query 8 times

select count(*) from master..spt_values where type <> 'P'

Run this stored procedure 9 times

exec prTestProc

Now let’s look at the output. Here is the query that returns all the queries, their execution counts, if they were ad hoc or not and their last execution time. The query works by using the sys.dm_exec_query_stats and sys.dm_exec_sql_text dynamic management views to bring back the SQL statements themselves.

SELECT * FROM(SELECT coalesce(object_name(s2.objectid),'Ad-Hoc') as ProcName,execution_count, 
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
where sql_statement like '%spt_values%'
AND sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count desc

Here is the output

ProcName   execution_count sql_statement                           last_execution_time
prTestProc  9       select * from master..spt_values where type = 'P'       2009-08-03 10:11:38.810
Ad-Hoc      8       SELECT COUNT(*) FROM [master]..[spt_values] WHERE [type]<>@1    2009-08-03 10:11:22.857
Ad-Hoc      7       select count(*) from master..spt_values             2009-08-03 10:11:19.107
Ad-Hoc      6       select count(*) from master..spt_values where type = 'P'    2009-08-03 10:11:15.760
Ad-Hoc      5       select * from master..spt_values where type = 'P'       2009-08-03 10:11:12.280

Let’s look at the query in more detail

This line below has the name of the table we are searching for

where sql_statement like '%spt_values%'

The line below excludes the query that we are running itself since that is not what we want to return

AND sql_statement NOT like 'SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid)%'

The line below will return Ad Hoc or the name of the object that the code was in, if s2.objectid is NULL then it was an Ad-Hoc query

coalesce(object_name(s2.objectid),'Ad Hoc')

As you can see this is probably good enough to give you some quick results to find out if a table is used so that you can drop it. The way I do this is I rename the table by prefixing it with 2 underscores, this enables two things for me

1) I can quickly find the table since it will be on top in the object explorer

2) I don’t have to think what the original name was, I just remove the 2 leading underscores

Of course you can also run a trace and then store that in a file, this enables you then to parse the file with a trace tool (more on that later this week when I will blog about a couple of free trace tools, Ami Levin just notified me about a new free trace tool so I will include that one). You could also use Extended Events to do something like this but I like this approach because it is very simple and you can all do it in T-SQL

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum