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.
| 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 wasSELECT * FROM sys.tables; |
 |
|
|
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 wasSELECT * 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_DIFFFROM SYS.TABLESWHERE MODIFY_DATE<=GETDATE()-180AND TYPE='U' |
|
|
|
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. |
|
|
|
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_updatefrom 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' |
|
|
|
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. |
|
|
|
|
|
|
|
|
Delete un-used tables form the server