CODEWITHSUNDEEP
sqlsql-server
John
John

Reputation:

Drop tables by date it were created

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

Upvotes: 0

Views: 1814

Answers (1)

Thakur
Thakur

Reputation: 2020

For MS SQL Below script may serve your purpose

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'

Upvotes: 1

Related Questions