select *
from sys.tables t
where name like 'TMP_%'
and create_date < getdate() - 3
order by create_date desc
/*
CREATE PROCEDURE [DBA_MESSAGENT].[SP_System_Delete_TMP_Tables]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tableName nvarchar(128)
DECLARE cursorTables CURSOR FOR
select t.name from sys.tables t where t.Name like 'TMP_%' and create_date < getdate() - 3
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqlSelect NVARCHAR(4000)
SET @sqlSelect = 'DROP TABLE [' + @tableName + ']'
EXEC SP_EXECUTESQL @sqlSelect
FETCH NEXT FROM cursorTables INTO @tableName
END
CLOSE cursorTables
DEALLOCATE cursorTables
END
*/