delete multiple tables

i have a inherited adatabase application that creates a table everyday.
because a table is created evryday the database is becoming huge. after muc
h
deliberation i have decided that i only need to keep 30 days of tables. how
can i delete all tables older than 30 days inside the database? i am fairly
new to sql and i have not been able to figure out how to delete multiple
tables based on creation date.
ronnieHi Ronnie
Ur idea in deleting old tables looks good but never try to delete a table
based on the date they were created.
This might also delete your production tables in the database which are
important
create your tables for ex: ForDelete_<Table_name>_<date>
and now u can write a stored procedure to loop arround and delete these kind
of tables.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||See if this helps, but first be sure to have a backup of the db before using
it in production.
use your_db
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cd datetime
declare tables_older_than_30_days cursor local fast_forward
for
select
user_name(uid) as table_schema,
[name] as table_name,
crdate
from
sysobjects
where
xtype = 'U'
and objectproperty([id], 'IsMSShipped') = 0
and datediff(day, crdate, getdate()) > 30
open tables_older_than_30_days
while 1 = 1
begin
fetch next from tables_older_than_30_days into @.ts, @.tn, @.cd
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'drop table ' + quotename(@.ts) + N'.' + quotename(@.tn)
print @.sql
-- uncomment this line to drop the table
-- exec sp_executesql @.sql
end
close tables_older_than_30_days
deallocate tables_older_than_30_days
go
AMB
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||Ronny
Wow , it looks to me you have a poor designed database
Why do you/someone else need to create a table every day?
In sysobjects system table there is a cdate column .
"Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after
much
> deliberation i have decided that i only need to keep 30 days of tables.
how
> can i delete all tables older than 30 days inside the database? i am
fairly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||the database is for an automated vehicle location tacking. each day becomes
its own table. the table is populated with gps points for multiple vehicles
.
i am not sure why they set it up this way. i have not given it a lot of
thought yet. first i want to clean it up then look at redsigning it. thank
s
for the tip.
ronnie
"Uri Dimant" wrote:

> Ronny
> Wow , it looks to me you have a poor designed database
> Why do you/someone else need to create a table every day?
> In sysobjects system table there is a cdate column .
>
>
> "Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
> news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> much
> how
> fairly
>
>