September 9, 2015 at 12:24 am
Hi all,
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
September 9, 2015 at 1:07 am
ramezani583 21209 (9/9/2015)
Hi all,In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
Try this for a starter
😎
USE [db_name];
GO
SET NOCOUNT ON;
;WITH BASE_DATA AS
(
SELECT
DB_NAME(DIUS.database_id) AS DBNAME
,OBJECT_SCHEMA_NAME(DIUS.object_id) AS SCHEMANAME
,OBJECT_NAME(DIUS.object_id) AS TBL_NAME
,DIUS.index_id
,SUM
(
DIUS.user_seeks
+ DIUS.user_scans
+ DIUS.user_lookups
+ DIUS.user_updates
) OVER
(
PARTITION BY DIUS.object_id
) AS USAGE
,DIUS.user_seeks
,DIUS.user_scans
,DIUS.user_lookups
,DIUS.user_updates
,DIUS.last_user_seek
,DIUS.last_user_scan
,DIUS.last_user_lookup
,DIUS.last_user_update
,DIUS.system_seeks
,DIUS.system_scans
,DIUS.system_lookups
,DIUS.system_updates
,DIUS.last_system_seek
,DIUS.last_system_scan
,DIUS.last_system_lookup
,DIUS.last_system_update
FROM sys.dm_db_index_usage_stats DIUS
WHERE DIUS.database_id = DB_ID()
)
SELECT
BD.DBNAME
,BD.SCHEMANAME
,BD.TBL_NAME
,BD.index_id
,BD.USAGE
,BD.user_seeks
,BD.user_scans
,BD.user_lookups
,BD.user_updates
,BD.last_user_seek
,BD.last_user_scan
,BD.last_user_lookup
,BD.last_user_update
,BD.system_seeks
,BD.system_scans
,BD.system_lookups
,BD.system_updates
,BD.last_system_seek
,BD.last_system_scan
,BD.last_system_lookup
,BD.last_system_update
FROM BASE_DATA BD
ORDER BY BD.USAGE ASC
;
September 9, 2015 at 1:39 am
Eirikur, a semi-colon before a CTE? I expected better from you! 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 9, 2015 at 1:50 am
Phil Parkin (9/9/2015)
Eirikur, a semi-colon before a CTE? I expected better from you! 🙂
Phil, not going into the begininator/terminator debate 😀 as this is a habit for the convenience of copy/paste, I can drop this anywhere into the code without even thinking what precedes it.
😎
September 9, 2015 at 4:49 am
Eirikur Eiriksson (9/9/2015)
Phil Parkin (9/9/2015)
Eirikur, a semi-colon before a CTE? I expected better from you! 🙂Phil, not going into the begininator/terminator debate 😀 as this is a habit for the convenience of copy/paste, I can drop this anywhere into the code without even thinking what precedes it.
😎
I know that you know what you're doing. But others might read your code who are less proficient than you. In these cases, it is helping to perpetuate the myth that CTEs should begin with a ;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply