Wednesday 6 October 2010

Check if Global Temp Table Already Exists in SQL Server 2005

One problem with using Global temp tables in SQL Server, (those indicated by a double hash symbol at the beginning of their name), is that a procedure fails to remove them, the next time they are created you will get an error because they already exists.

To test for their existence before creating them in SQL Server 2005 I used this code, replace ##temp with your table name of course


IF NOT EXISTS(select * from tempdb.sys.sysobjects where name = '##Temp')
BEGIN
                       code to create ##temp
        END
        ELSE
        BEGIN
                      truncate table ##temp
        END

No comments: