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! 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 ;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply