SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Delete un-used tables form the server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

misterdeey
Starting Member

USA
19 Posts

Posted - 06/18/2013 :  12:19:05  Show Profile  Reply with Quote
Hi,

I need to delete all tables that have been created or have data inserted into them prior to getdate()-180. How can do it?

Thank you

Edited by - misterdeey on 06/18/2013 12:53:04

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/18/2013 :  13:05:30  Show Profile  Reply with Quote
You can use sys.tables to find information on when a table was created or last modified. However, unless you have some type of audit information or update timestamp column in your tables, there is nothing that I know of built into SQL Server features that will tell you when the last insert was
SELECT * FROM sys.tables;
Go to Top of Page

misterdeey
Starting Member

USA
19 Posts

Posted - 06/18/2013 :  13:35:53  Show Profile  Reply with Quote
quote:
Originally posted by James K

You can use sys.tables to find information on when a table was created or last modified. However, unless you have some type of audit information or update timestamp column in your tables, there is nothing that I know of built into SQL Server features that will tell you when the last insert was
SELECT * FROM sys.tables;





James,

Thank you for your input, I've tried that table with the below script but I could not find the last inser date within my tables, as the MODIFY_DATE is not reflecting the last date rows were inserted to the tables and I'm afraid to drop tables that I still need.


SELECT DISTINCT OBJECT_ID,NAME,TYPE,TYPE_DESC,
(CAST(FLOOR(CAST(CREATE_DATE as FLOAT)) AS DateTime)) AS CREATION_DATE,
CAST(GETDATE() as FLOAT) -CAST(CREATE_DATE as FLOAT) AS CREATION_DATE_DIFF,
(CAST(FLOOR(CAST(MODIFY_DATE as FLOAT)) AS DateTime))AS DATE_MODIFIED,
CAST(GETDATE() as FLOAT) -CAST(MODIFY_DATE as FLOAT) AS MODIFY_DATE_DIFF
FROM SYS.TABLES
WHERE MODIFY_DATE<=GETDATE()-180
AND TYPE='U'
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/18/2013 :  14:06:27  Show Profile  Reply with Quote
The modify_date does not indicate data modification date. It indicates the "schema" modification date - i.e., the last date on which someone added or removed a column in the table, changed the data type of a column etc.

As I had described earlier, there is nothing inherently built into SQL Server that tells you when a row in a table was updated/inserted/deleted. People usually add an "UpdatedDatestamp" column to the tables and update it with each update of the table via a trigger or other means to capture this information. If the details of the update also need to be preserved, an audit table with that information and the update timestamp would be used.
Go to Top of Page

misterdeey
Starting Member

USA
19 Posts

Posted - 06/18/2013 :  14:22:33  Show Profile  Reply with Quote
quote:
Originally posted by James K

The modify_date does not indicate data modification date. It indicates the "schema" modification date - i.e., the last date on which someone added or removed a column in the table, changed the data type of a column etc.

As I had described earlier, there is nothing inherently built into SQL Server that tells you when a row in a table was updated/inserted/deleted. People usually add an "UpdatedDatestamp" column to the tables and update it with each update of the table via a trigger or other means to capture this information. If the details of the update also need to be preserved, an audit table with that information and the update timestamp would be used.




I've found the below script that is providing the last time a table has been touched. However, it does not display all tables that I have:



select t.name, user_seeks, user_scans, user_lookups,
user_updates, last_user_seek, last_user_scan,
last_user_lookup, last_user_update
from sys.dm_db_index_usage_stats i
JOIN sys.tables t
ON (t.object_id = i.object_id)
where database_id = db_id()
and TYPE='U'

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/18/2013 :  15:44:54  Show Profile  Reply with Quote
As you found out, that is not really a reliable indicator.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000