| Author |
Topic |
|
misterdeey
Starting Member
19 Posts |
 Posted - 2012-03-30 : 14:38:21
|
| I need to do a server cleanup ro drop any table that has not been used or updated for over 6 months. To determine all impacted tables I'm using the below query:SELECT OBJECT_ID,NAME,TYPE,CREATE_DATE AS CREATION_DATE,MODIFY_DATE AS DATE_MODIFIEDFROM SYS.TABLESWHERE MODIFY_DATE<=GETDATE()-180ORDER BY 5Now how can I drop all these tables at once?Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
misterdeey
Starting Member
19 Posts |
Posted - 2012-03-30 : 15:03:28
|
| I do have a protected flag on another local table and from which a filter will be used to avoid dropping the good tables :)Thanks much for your help. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-30 : 16:41:08
|
quote:
Originally posted by X002548
How does modify_date when a row has been inserted updated or deleted?
It doesn't.From Books Online:quote:
modify_date Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-30 : 16:42:53
|
quote:
Originally posted by misterdeey
I need to do a server cleanup ro drop any table that has not been used or updated for over 6 months. To determine all impacted tables I'm using the below query:SELECT OBJECT_ID,NAME,TYPE,CREATE_DATE AS CREATION_DATE,MODIFY_DATE AS DATE_MODIFIEDFROM SYS.TABLESWHERE MODIFY_DATE<=GETDATE()-180ORDER BY 5Now how can I drop all these tables at once?
That query will get you tables that have not been altered (ALTER TABLE) in the last 6 months. It could easily return the most active table in your database, the one that got several thousand inserts in the last 2 hours.Sure that's what you want?--Gail ShawSQL Server MVP |
|
|
|
misterdeey
Starting Member
19 Posts |
Posted - 2012-03-30 : 17:20:55
|
| X002548 => That's where our local table that host all protected tables' names will come in play :) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-30 : 17:48:12
|
| Are you looking for tables that have not had any data modification or access in a period of time or tables that have not had a schema (definition change) in a period of time (ignoring any protected table lists)?--Gail ShawSQL Server MVP |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2012-03-30 : 20:29:23
|
Misterdeey,Dropping tables that haven't been used in 180 days does not make much sense unless the database is filled with just arbitrary data that has no correlation to anything, and even then there are many questions as to WHY/How did you get so many tables that have no use and need to be dropped?There is likely a much better approach to cleaning up your data/tables than what you have presented. Can you shed some light on why you have so many tables that nothing ties to or will need, and the logic behind why it will be OK to drop them if they have not been updated in 180 days?Odds are someone here can point you in a better direction, but it sounds like a VERY RISKY way to clean up your data, and I'd suggest holding off until you get more information, and post the issue you are having along with how you have so many unnecessary tables in your database that you know are safe to remove..
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
|
How can I drop multiple tables from SQL 2008?