Delete tables older than 5 days

Something like this?

DELETE FROM <tablename1> WHERE datediff(now(), statusDate) > 5

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

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".

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen in new window

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)

Select allOpen in new window

>>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...

Thanks Paul and yes it does return the correct tables :)