Find usage of Tables

Category: sql server transactsql

Question

coolguy123SQL on Fri, 12 Aug 2016 19:16:02


Hi i am trying to find out

Tablename No Rows LastRead           LastWrite              HasAnyDependancy

Abc              Yes        03/01/2016     08/12/2016            yes 

Xyz              no       03/01/2012        NULL                   yes 

I have bunch of T-SQL queries to find all above mentioned details as a Separate queries

Is there a way to find in 1 Query ?

I am trying to find usage of tables and then get rid if unused ones

Thanks, 

 

Replies

Erland Sommarskog on Fri, 12 Aug 2016 21:40:41


Since I don't see your current queries, it is difficult to comment on whether they can be consolidated into a single one. But since SQL Server does not automatically track when a table last was read or written to, you need to have something going to retrieve this data. SQL Audit?

coolguy123SQL on Fri, 12 Aug 2016 23:13:52


----tables with no rows 
SELECT t.name, p.rows
FROM sys.tables t
JOIN sys.partitions p ON t.[object_id]=p.[object_id]
WHERE p.rows=0
AND t.is_ms_shipped=0

---dependency list 
SELECT  DB_NAME() AS dbname, 
 o.type_desc AS referenced_object_type, 
 d1.referenced_entity_name, 
 d1.referenced_id, 
        STUFF( (SELECT ', ' + OBJECT_NAME(d2.referencing_id)
   FROM sys.sql_expression_dependencies d2
         WHERE d2.referenced_id = d1.referenced_id
                ORDER BY OBJECT_NAME(d2.referencing_id)
                FOR XML PATH('')), 1, 1, '') AS dependent_objects_list
FROM sys.sql_expression_dependencies  d1 JOIN sys.objects o 
  ON  d1.referenced_id = o.[object_id]
GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name
ORDER BY   d1.referenced_entity_name

---table last acessesd

SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
GROUP BY ius.[database_id], ius.[object_id];

those are the three queries i use to find out below:

No Rows

LastRead          

LastWrite              

HasAnyDependancy

Kalman Toth on Fri, 12 Aug 2016 23:40:19


>I am trying to find usage of tables and then get rid if unused ones

I suggest the following:

1. Setup a trace for the tables in question and run it for a week or month:

How to monitor activity on a single table using SQL Server profiler

2. If no usage in a week or month, change the table name by prefixing "zzzToBeDroppedNov2016"

3. In half a year's time email notify everybody about the intended table drop

4. Take a database backup; Drop the tables if no one comes forward to keep them



Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016





msbi_Dev on Sat, 13 Aug 2016 00:16:57





SELECT 
b.Tablename , b.[No Rows] ,Access.LastRead ,  Access.LastWrite , CASE WHEN   Dependancy.dependent_objects_list IS NULL THEN 'No' ELSE 'Yes' END HasAnyDependancy
FROM 
(  
 SELECT   t.object_id ,  Tablename= OBJECT_NAME(t.object_id )  , [No Rows] = CASE WHEN  SUM(p.rows) = 0 THEN 'Yes' ELSE 'No' END 
 FROM   AdventureWorksDW2012. sys.tables t 
 JOIN    AdventureWorksDW2012.sys.partitions p ON t.[object_id]=p.[object_id]
 WHERE   t.is_ms_shipped=0   
 GROUP BY t.object_id 
 ) b

 LEFT JOIN 
 (
		---table last acessesd
		SELECT  ius.object_id,   
		LastRead /*  Scan /Seek */= CASE WHEN MAX(last_user_seek) > MAX(last_user_scan) THEN MAX(last_user_seek) ELSE  MAX(last_user_scan)   END  ,
		LastWrite =  MAX(last_user_update)
		FROM sys.dm_db_index_usage_stats AS ius  WHERE  ius.database_id= DB_ID()
		GROUP BY  ius.object_id
  ) Access ON Access.object_id = b.object_id
   
 LEFT JOIN 
  (
   

		  SELECT   
		  d1.referenced_id  AS object_id , 
				STUFF( (SELECT ', ' + OBJECT_NAME(d2.referencing_id)
		   FROM sys.sql_expression_dependencies d2
				 WHERE d2.referenced_id = d1.referenced_id
						ORDER BY OBJECT_NAME(d2.referencing_id)
						FOR XML PATH('')), 1, 1, '') AS dependent_objects_list
		FROM sys.sql_expression_dependencies  d1 JOIN sys.objects o 
		  ON  d1.referenced_id = o.[object_id]
		GROUP BY d1.referenced_id 


  ) Dependancy ON Dependancy.object_id = b.object_id


Erland Sommarskog on Sat, 13 Aug 2016 08:31:52


----tables with no rows 
SELECT t.name, p.rows FROM sys.tables t JOIN sys.partitions p ON t.[object_id]=p.[object_id]
WHERE p.rows=0 AND t.is_ms_shipped=0

Beware that this will give incorrect information if you have a partitioned table with an empty partition, or a filtered index where no rows currently qualify for the filter.

This would be better:

SELECT s.name, o.name,
       hasrows = CASE WHEN EXISTS (SELECT *
                                   FROM   sys.partitions p
                                   WHERE  o.object_id = p.object_id
                                     AND  p.rows > 0)
                           THEN 'Yes'
                           Else 'No'
                 END
FROM   sys.objects o
JOIN   sys.schemas s ON o.object_id = s.object_if
WHERE  o.type = 'U'

That is, find tables with a least one non-empty partition.

---dependency list 
SELECT  DB_NAME() AS dbname, 
 o.type_desc AS referenced_object_type, 
 d1.referenced_entity_name, 
 d1.referenced_id, 
        STUFF( (SELECT ', ' + OBJECT_NAME(d2.referencing_id)
   FROM sys.sql_expression_dependencies d2
         WHERE d2.referenced_id = d1.referenced_id
                ORDER BY OBJECT_NAME(d2.referencing_id)
                FOR XML PATH('')), 1, 1, '') AS dependent_objects_list
FROM sys.sql_expression_dependencies  d1 JOIN sys.objects o 
  ON  d1.referenced_id = o.[object_id]
GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name
ORDER BY   d1.referenced_entity_name

If you only want yes/no, the above could be simplified to an EXISTS like above as well.

---table last acessesdSELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
GROUP BY ius.[database_id], ius.[object_id];

Beware that these values are reset after a restart of SQL Server. Therefore they not wholly reliable. For isntance, there may be tables that only in play with the end-of-quarter of reporting, and therefore will come out as unusued if you look at the wrong moment.

Given this caveat, you could use this as the base query to which you add the EXISTS queries I suggested above.

coolguy123SQL on Mon, 15 Aug 2016 13:39:48


Thanks Rajiv

coolguy123SQL on Mon, 15 Aug 2016 13:40:02


Thanks for the info Erland

coolguy123SQL on Mon, 15 Aug 2016 13:40:11


Thanks for the info Kalman