SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find tables unused since last SQL Server restart


Find tables unused since last SQL Server restart

Author
Message
Ryan McCauley
Ryan McCauley
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 162
Comments posted to this topic are about the item Find tables unused since last SQL Server restart

Ryan
GilaMonster
GilaMonster
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119315 Visits: 45555
It's not since the last SQL start, it's since the last database start. If the DB is closed, taken offline, detached or restored, those stats are also cleared. Watch out for DBs set to autoclose

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


fjmorales
fjmorales
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 283
I’m trying to run this t-sql but it gives me this error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ' '.
John McC
John McC
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1678
Same error here.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. Crazy
GilaMonster
GilaMonster
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119315 Visits: 45555
It looks like there are some non-printable characters that aren't spaces but look like spaces. Edit the whole thing onto a single line then re-format. Seems to work

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


John McC
John McC
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1678
Using http://extras.sqlservercentral.com/prettifier/prettifier.aspx



WITH LastActivity (ObjectID, LastAction) AS
(
SELECT OBJECT_ID AS TableName,
last_user_seek AS LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = DB_ID(DB_NAME())
UNION
SELECT OBJ
ECT_ID AS TableName,
last_user_scan AS LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = DB_ID(DB_NAME())
UNION
SELECT OBJECT_ID AS TableName,
last_user_lookup AS LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = DB_ID(DB_NAME())
)
SELECT OBJECT_NAME(so.OBJECT_ID) AS TableName,
MAX(la.LastAction) AS LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
ON so.OBJECT_ID = la.ObjectID
WHERE so.TYPE = 'U'
AND so.OBJECT_ID > 100
GROUP BY OBJECT_NAME(so.OBJECT_ID)
ORDER BY OBJECT_NAME(so.OBJECT_ID)



-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. Crazy
jfordyce
jfordyce
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 29
Is there a version of this script that would show the last time a SP was executed?? I'm really in need of that one Smile
fjmorales
fjmorales
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 283
Thanks!!!
Ryan McCauley
Ryan McCauley
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 162
Thanks for re-formatting the query - I'll see if I can get that fixed on the posted version of the script.

As far as Stored procs, it looks like you can do the same kind of thing as long as it's still in the cache, but not if the server has been restarted or the cache flushed since it was last run (much as with this script):

SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),
sys.dm_exec_query_stats.*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)
WHERE sys.dm_exec_sql_text.dbid = db_id()
AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'yoursp'

Ryan
David-CH
David-CH
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 187
copy the script onto Notepad. Remove all those tiny rectangular shapes (which were interpreted as spaces which cause errors), then copy the whole script back onto SSMS, parse again. You should get no error this time.:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search