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,
LVL 21
pinaldaveAsked:
Who is Participating?
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
pai_prasadCommented:
u can get the datecreated from SYSOBJECTS crdate column
0
 
pai_prasadCommented:
change it to

and crdate <= dateadd(m,-1,getDate())
0
 
pinaldaveAuthor Commented:
Wonderful Thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.