Delete tables older than 5 days

I have a series of tables that are auto-created for use in calculations as actual temp tables were unable to be used.

How can I drop sets of tables like the attached if they were created more than 5 days ago?
tables.JPG
tables2.JPG
PeterErhardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick BogersDatacenter platform engineer LindowsCommented:
Something like this?

DELETE FROM <tablename1> WHERE datediff(now(), statusDate) > 5
0
Easwaran ParamasivamCommented:
While creating a table have a column CreatedDtm. While inserting a value insert getdate() for it.

Then use below query to identify and delete it.

IF EXISTS(SELECT 1 FROM TableName where CreatedDtm > DATEADD (dd, 5, getdate())
 DROP TABLE  TableName
0
PeterErhardAuthor Commented:
Patricksr1972 - I need to drop the table, not just delete from it

EaswaranP - that's a good idea. How can I get a list of the tables though? I need to include your code in a loop of all the tables starting with "tempdismissals" and "usr_tmp".
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Easwaran ParamasivamCommented:
Below code will give the list of tables for you:

DECLARE @TableName as varchar(100)
DECLARE @SearchString AS NVARCHAR(1000)

SET @SearchString =  '(CreatedDtm > DATEADD (dd, 5, getdate() )'
DECLARE MYCURSOR CURSOR 
FOR 
	SELECT '['+SCHEMA_NAME(T.schema_id)+'].['+T.NAME+']'  FROM
	sys.tables T

OPEN MYCURSOR

FETCH NEXT FROM MYCURSOR 
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY
		
		EXEC('
		IF (SELECT COUNT(*) FROM ' + @TableName + ' nolock WHERE ' + @SearchString + ')>0
		BEGIN
			SELECT ''' + @TableName + ''' 
			
		END')
	END TRY
	BEGIN CATCH
		--SELECT 'No Rows Returned'
	END CATCH
                                            
	FETCH NEXT FROM MYCURSOR 
	INTO @TableName
END 
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

Open in new window

0
PeterErhardAuthor Commented:
I've managed to form the following query which seems to be a bit better. Especially handy with the CREATE_TIME.

The problem is the following clause isn't working:

AND DATEDIFF( NOW( ) , CREATE_TIME ) <5

Open in new window


Any ideas why?

SELECT table_name, CREATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA =  'databasename'
AND
 (
table_name LIKE  'tempdismissals%'
OR table_name LIKE  'usr_tmp%'
)
AND DATEDIFF( NOW( ) , CREATE_TIME ) <5

Open in new window

0
PortletPaulfreelancerCommented:
re: Any ideas why?

datediff requires a unit of measure (e.g. day)

I would rather do it this way though:

AND CREATE_TIME < DATE_SUB(CURDATE(),INTERVAL 5 DAY) /* MySQL */
-- result has a time of 00:00:00
-- could use CURTIME() -or- NOW() instead

AND CREATE_TIME < DATEADD(DAY,-5,GETDATE()) /* MS SQL Server */
-- result will most likely have a time <> 00:00:00


There seems to be some MS SQL Server code being proposed here, but the topic is MySQL

Could you confirm which dbms the code is for please?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterErhardAuthor Commented:
Thanks Paul.

This now seems to do the trick perfectly and I can then get the table_names from this query and drop them accordingly.

Do you see any problems with it?

SELECT table_name, CREATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA =  'jamesn_statsspidertest'
AND
 (
table_name LIKE  'tempdismissals%'
OR table_name LIKE  'usr_tmp%'
)
AND CREATE_TIME < DATE_SUB(CURDATE(),INTERVAL 5 DAY)

Open in new window

0
PortletPaulfreelancerCommented:
>>Do you see any problems with it?
No, it looks OK to me

- but I would prefer to judge by the data returned which only you can do as I would not recognize the tables anyway

but, as I'm here...
----
The reason for suggesting that where condition construct:

AND CREATE_TIME < DATE_SUB(CURDATE(),INTERVAL 5 DAY)
is that it is "Sargable"
and it is not applying a function at every row of data
0
PeterErhardAuthor Commented:
Thanks Paul and yes it does return the correct tables :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.