It can rather hard to peek inside a stored proc...
...especially one that is called from the job task scheduler. Best I can recommend is to emulate a print statement type of thing - but since PRINT isn't output you have to mimic it though tables. Specifically, create a dummy table that has a text column. Then do an insert from that table within the stored proc - inserting text that has some meaning as to what the text processing is actually doing.
In this case, I'd insert the names of the tables that match the criteria, since my first suspicion would be that the query on the systables is not returning any rows (thus shortcircuiting the rest of the logic). If the tables appear to be selected properly, then output the text that is used in the execute statement to see if it is properly formed sql (since that'd be my second suspicion).
Though this form of debugging may seem crude, it's miles above some of the debugging in real-time apps, where you're only form of getting some feedback may be a single LED that you have to make give you some information about what's going on inside.
[Edit Note: Of the top of my head, something along the lines of]
\n-- create a table to get some feedback\nCREATE TABLE PrimitiveDebugger(PrintTime DATETIME, PrintText TEXT)\nGO\n\n-- then, as before in the stored proc, get a pulse from within\n\n-- find if the select population is correct\nINSERT INTO PrimitiveDebugger\nSELECT GETDATE(), Name\nFROM sysobjects tab\nWHERE (tab.type = 'U')\n AND (DATEDIFF(hh, tab.crdate, GETDATE()) > 6)\n AND (tab.name LIKE('Temp%'))\n\nDECLARE @TableName VARCHAR(255)\nDECLARE #csrTable CURSOR FAST_FORWARD READ_ONLY FOR\n SELECT Name\n FROM sysobjects tab\n WHERE (tab.type = 'U')\n AND (DATEDIFF(hh, tab.crdate, GETDATE()) > 6)\n AND (tab.name LIKE('Temp%'))\nOPEN #csrTable\nFETCH NEXT FROM #csrTable INTO @TableName\nWHILE (@@FETCH_STATUS != -1)\n BEGIN\n INSERT INTO PrimitiveDebugger VALUES(GETDATE(), 'DROP TABLE ' + @TableName)\n --EXECUTE('DROP TABLE ' + @TableName)\n FETCH NEXT FROM #csrTable INTO @TableName\n END\nCLOSE #csrTable\nDEALLOCATE #csrTable\n\nGO\n\n-- from query analyzer after the task runs\nSELECT * FROM PrimitiveDebugger ORDER BY PrintTime\nGO\n