Drop tables by date it were created

Question

Hi,

I have a large database that were used to archive tables before implementing structural change on it.

We had this database for years, I want to create a dynamic script to check today's date and drop any table in this database that were created 3 years or older.

Thanks

Answer 1

sys.objects has a create_date column. Pick out all the tables (type = U) with a create date you don't want to keep and generate a drop table statement and execute the statement.

---

Tom

Answer 2

Hello,

Adding Code

SELECT 'DROP TABLE ' + name
FROM  sys.objects
WHERE Datediff(YEAR, create_date, Getdate()) > 3
    AND TYPE = 'U' 

Thanks

Answer 3

one more way to get this :

select       'drop table  [' + s.name +'].[' + t.name +']' , t.create_date  
from  
    sys.tables t 
inner join 
    sys.schemas s 
    on 
    s.schema_id = t.schema_id 
where  
    create_date< DATEADD(year,-3, GETDATE()) 
     and type='U'