• TheSQLGuru (1/16/2014)


    Not enough time to check, but does tempdb.sys.tables show temp objects that aren't yours? Also, does it provide a name that is "droppable"?

    Yes, the name that is in tempdb.sys.tables is "droppable" but yes it will also have all the additional characters like at the end of the table name "________________________________________________________________________________________________________________0000000E300B" at the end. Just in case you end up with some stupid naming conventions of temp tables in your script you can use the following just to be safe:

    DECLARE @sql NVARCHAR(MAX) = N''

    SELECT

    @sql = @sql + CHAR(10) + N'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME])

    FROM [tempdb].[INFORMATION_SCHEMA].[TABLES]

    PRINT @sql

    EXEC sp_executesql @sql

    Though you cannot recreate a table in the tempdb with the same name as a table created earlier in the same batch. For example the following code will cause an error if run in a single batch, but would execute normally if run in separate batches or even if the tables were not created in tempdb:

    SELECT

    1 AS [var]

    INTO #tab

    DROP TABLE [#tab]

    SELECT

    1 AS [var]

    INTO #tab