We help IT Professionals succeed at work.

Script: How to delete Table created before some date

pinaldave
pinaldave asked
on
Medium Priority
573 Views
Last Modified: 2008-02-01
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,
Comment
Watch Question

CERTIFIED EXPERT

Author

Commented:
I have moved this question to MSSQL as it was in wrong TA.
CERTIFIED EXPERT

Author

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 ''?'''      
u can get the datecreated from SYSOBJECTS crdate column
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
change it to

and crdate <= dateadd(m,-1,getDate())
CERTIFIED EXPERT

Author

Commented:
Wonderful Thank you :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.