XML Feeds
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.