database cleanup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

database cleanup

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2007-08-10 : 15:46:01

Is there an easy way to set up a job to run weekly
to automatically drop the tables returned in the following
select statement?

-----------------------------------------------------------

select 'drop table ' + name + ';' from sys.all_objects
where type='U' and DATEDIFF(day, create_date, getdate()) > 60

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-11 : 00:52:22

You can create job to run it in scheduled time, check books online on how to create job.

helpme
Posting Yak Master

141 Posts

Posted - 2007-08-13 : 08:14:56

Just putting that statement in a job isn't going to delete the tables, though. I don't know how to pull the results of the 'select' statement back in and execute them. Right now I'm running the select in query analyzer, saving the results (drop statements) to a file, and then pasting the results back into query analyzer to drop the tables. I was hoping there might be an easy way to automate this process. I'll keep looking. Thanks for your reply.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-13 : 08:28:10

Try this

declare @table table(st varchar(2000))
insert into @table

select 'drop table ' + name + ';' from sysobjects
where type='U' and DATEDIFF(day, crdate, getdate()) > 60


declare @delete varchar(2000)
select @delete=min(st) from @table 

While @delete>''
Begin
	select @delete=min(st) from @table where st>@delete
	EXEC(@delete)
End

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources