[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

Script: How to delete Table created before some date

Hello,

I have Database named MyDatabase.
We have to create the tables for certain reasons. Though, we have to use those table for some days only. After that the table is considered as expired.
I want to delete the tables which are created before certain date. Example would be one month ago.
All our tables starts with "MyTmp_"?

How can I do using a script. I can write scheduled job which does this once a month.

Kindly guide me.

Regards,
0
pinaldave
Asked:
pinaldave
  • 3
  • 3
1 Solution
 
pinaldaveAuthor Commented:
I have moved this question to MSSQL as it was in wrong TA.
0
 
pinaldaveAuthor Commented:
I can use this syntax to retrive all the size of the table. Can I also retrive the datecreated of the table ?

EXEC       sp_msforeachtable 'sp_spaceused ''?'''      
0
 
pai_prasadCommented:
u can get the datecreated from SYSOBJECTS crdate column
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
pai_prasadCommented:
i think tis shud suffice


Set noCount on
Declare @lcl_name varchar(100)

Declare cur_name cursor for
Select name
from sysobjects
where type = 'U'
      and crdate <= dateadd(m,1,getDate())
      and name like 'MyTmp_%'

open cur_name
fetch next from cur_name into @lcl_name
While @@Fetch_status = 0
Begin
      Select @lcl_name  = 'Drop table ' +@lcl_name
      exec (@lcl_name )

fetch next from cur_name into @lcl_name
End

close cur_name
deallocate cur_name
      
Set noCount off
0
 
pai_prasadCommented:
change it to

and crdate <= dateadd(m,-1,getDate())
0
 
pinaldaveAuthor Commented:
Wonderful Thank you :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now