Monday, December 12, 2011

Finding How Many Times A Table Is Being Used In Stored Procedure or Ad-hoc Query

I have wrote below query for finding How many times a tables is being used in Stored Procedure or Ad-hoc Query. In this I used DMV's so this query only works on 2005 and later versions like 2008, and 2012. As you know DMV's keep the information since last restart of the Instance. If server is not restarted in many days, you will get good result through this query. One drawback of this query is if the table name matches with other objects Like view then the result will not be accurate, it will pull the information of both table and view. 

Let Start with an example:
Step 1: Create a Stored Procedure using below script. 
 USE AdventureWorks
GO
CREATE PROC USP_TestingSP
AS
SELECT * FROM AdventureWorks.Person.Address
GO

 
Step 2: Now Execute the Stored Procedure. ( i executed this SP couple of times for testing)

EXEC USP_TestingSP


Step 3:  Now run the below script for selecting entire data from table. (I executed below query 3 times for testing)

SELECT * FROM AdventureWorks.Person.Address



Step 4: Now run the below script for counting no of rows in the table. (I executed this only once)

SELECT COUNT (*) FROM AdventureWorks.Person.Address



Step 5: Now finally run the main query for finding the how many times a table is being used in Stored Procedure or Ad-hoc Query.

USE AdventureWorks  ---Change the database name with your database. 
GO 
SELECT * FROM(SELECT COALESCE(OBJECT_NAME(A2.objectid),'Ad-Hoc') AS ProcedureName,execution_count, 
(SELECT TOP 1 SUBSTRING(A2.TEXT,statement_start_offset / 2+1 , 
( (CASE WHEN statement_end_offset = -1 
THEN (LEN(CONVERT(NVARCHAR(MAX),A2.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 A1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS A2 ) x 
WHERE sql_statement like '%Address%' ---Change the table name with yours.(In my case Person.Address) 
AND sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(A2.objectid)%'


I hope you understood the above example, if you have any questions please leave a comment.
Finding Out How Many Times A Table Is Being Used In Ad Hoc Or Procedure Calls In SQL Server 2005 And 2008. sql server 2012. Multiplication table. table usage sql server. Determine Size of a Table in SQL Server. Sql server table usage statistics. Getting Table Space Usage in SQL Server 2012. Find Table Usage . Find Stored Procedure Related to Table in Database. How Can I Determine Which Tables and Indexes Are Not Being used. table being used. sql server space used table. sql server find table used. sql server most used tables. sql server database last used. use temporary tables sql server.

0 comments:

Post a Comment