Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Delete un-used tables form the server

Author  Topic 

misterdeey
Starting Member

19 Posts

Posted - 2013-06-18 : 12:19:05
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 13:05:30
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

19 Posts

Posted - 2013-06-18 : 13:35:53
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 14:06:27
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

19 Posts

Posted - 2013-06-18 : 14:22:33
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 15:44:54
As you found out, that is not really a reliable indicator.
Go to Top of Page
   

- Advertisement -