Delete oldest table

[Rachel]

Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!

[Jaya]

try this

select name from sysobjects where type = 'U' order by crdate

You will get all the table names ordered by table created date

HTH
Jaya

------------
Rachel at 7/4/01 6:53:22 PM

Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!

[Ivo]

I think problem is that age of table is determined by NAME, not by CRDATE
try this one :

DECLARE @v datetime, @name varchar(30), @cmd char(50)
SELECT @v = MIN(CONVERT(datetime, SUBSTRING(name, 10, 4)+SUBSTRING(name, 7, 2)+'01', 112))
FROM sysobjects
WHERE <*here you must write command selecting names for your historical tables*>
SELECT @name = name
FROM sysobjects
WHERE <*same command as above in this brackets*>
AND CONVERT(datetime, SUBSTRING(name, 10, 4)+SUBSTRING(name, 7, 2)+&#39;01&#39;, 112) = @v
IF @name IS NOT NULL
BEGIN
SELECT @cmd = &#39;DROP TABLE &#39;+@name
EXEC @cmd
END



------------
Jaya at 7/5/01 12:50:53 AM


try this

select name from sysobjects where type = &#39;U&#39; order by crdate

You will get all the table names ordered by table created date

HTH
Jaya

------------
Rachel at 7/4/01 6:53:22 PM

Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!

[Rachel]

Thanks a lot Ivo - it works a treat.


------------
Ivo at 7/5/01 6:16:08 AM

I think problem is that age of table is determined by NAME, not by CRDATE
try this one :

DECLARE @v datetime, @name varchar(30), @cmd char(50)
SELECT @v = MIN(CONVERT(datetime, SUBSTRING(name, 10, 4)+SUBSTRING(name, 7, 2)+&#39;01&#39;, 112))
FROM sysobjects
WHERE <*here you must write command selecting names for your historical tables*>
SELECT @name = name
FROM sysobjects
WHERE <*same command as above in this brackets*>
AND CONVERT(datetime, SUBSTRING(name, 10, 4)+SUBSTRING(name, 7, 2)+&#39;01&#39;, 112) = @v
IF @name IS NOT NULL
BEGIN
SELECT @cmd = &#39;DROP TABLE &#39;+@name
EXEC @cmd
END



------------
Jaya at 7/5/01 12:50:53 AM


try this

select name from sysobjects where type = &#39;U&#39; order by crdate

You will get all the table names ordered by table created date

HTH
Jaya

------------
Rachel at 7/4/01 6:53:22 PM

Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!

[Tom]

Rachel,

Just my 2 cents, but I would think about having only one historical table, and then adding a column(s) that describe the month/year of the data. Then whenever you want to select/delete data you just call one table with the month/year as the criteria.

Tom


------------
Rachel at 7/4/01 6:53:22 PM

Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!