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


Deleting the tables which are not been used from long time.


Deleting the tables which are not been used from long time.

Author
Message
ksr39
ksr39
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 217
HI Experts,
Actually we are planning to replicate the prod data to our DR center. So, thinking to replicate only that data which are used, so I need a help in getting the tables which are not been used so that I can delete them, and replicate only those tables which are in use. I tried but couldn’t find the exact way. So, please help me in finding the exact tables which are not been used. And more over how to find the tables which are been used in Sp’s and Views, Where I found some tables which are not in the code but they are available in database.
Thank you in advance
Bhuvnesh
Bhuvnesh
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32270 Visits: 4079
ksr39 (10/10/2012)
I tried but couldn’t find the exact way. So, please help me in finding the exact tables which are not been used. And more over how to find the tables which are been used in Sp’s and Views, Where I found some tables which are not in the code but they are available in database.
there is no straight forward way to find those tables only secured approach is rename the tables which you think are no more in use like "test" to "dm_test", you can easily find that when and where this particular table for used (error will be there if it stil in use ) another thing if you dont get any issue then it means that table is not in use.:-D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ksr39
ksr39
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 217
Thank You Bhuvnesh for the quick reply,

But it’s difficult to find the table which is not been used more than a month as my server was rebooted since 65 days. If I can get an exact table names so that I can change their names it’s a good thing but when I am not sure with the table name then it will be over work in doing this. So is there any way other than this where I can get the tables info which are not been used more than a month or more.

Thank you in Advance
pujain
pujain
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1565 Visits: 241
you can use the below script to find when the table got accessed last time

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('DBNAME')
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id('DBNAME')
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id('DBNAme')
)
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)
durai nagarajan
durai nagarajan
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 2880
ksr39 (10/10/2012)
Thank You Bhuvnesh for the quick reply,

But it’s difficult to find the table which is not been used more than a month as my server was rebooted since 65 days. If I can get an exact table names so that I can change their names it’s a good thing but when I am not sure with the table name then it will be over work in doing this. So is there any way other than this where I can get the tables info which are not been used more than a month or more.

Thank you in Advance


if an application is depreciated why the relavent tables not deleted from production server or any debugging happening directly on production server?

Regards
Durai Nagarajan
Sean Lange
Sean Lange
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148202 Visits: 18571
Be careful with approach. Deleting tables just because they haven't been used in a couple months can very likely come back to bite you. What about reports that are only run once a year or other types of similar processes that are very important but not frequent?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ksr39
ksr39
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 217
Hi PuJain,

Thank you for the script, when I run the query I found that Last Select is ‘Null’ what does this 'Null' mean.
crmitchell
crmitchell
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2373 Visits: 1963
Sean Lange (10/10/2012)
Be careful with approach. Deleting tables just because they haven't been used in a couple months can very likely come back to bite you. What about reports that are only run once a year or other types of similar processes that are very important but not frequent?


Definitely this.
I would not be comfortable with this anywhere near DR.
If the worst happens and you need to recover its much better to find you have recoverred something you no longer need than to find that vital piece of data used for e.g. your annual tax return is missing.

By all means check through your databases to identify items which are no longer needed but dont mix this up with disaster recovery.
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