I have a SP where I check for the existence of table and if exists drop it IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ Temptabels') AND type in (N'U')) DROP TABLE [dbo].[Temptable] then i create the table When I execute the SP I get an error that the table already exists Msg 2714, Level 16, State 6, Procedure name, Line 53 There is already an object named 'Temptable in the database. Any ideas on this thanks a lot
IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable IF OBJECT_ID('tempdb..##temptable') IS NOT NULL DROP TABLE ##temptable IF OBJECT_ID('Local_TempTable') IS NOT NULL DROP TABLE Local_TempTable
If you have created the table TempTable using the # prefix to make use of a temporary table while code executes then this isnt created in the database where you code is executing, it is created in TempDB. @cymayorga has the code samples that you need to drop a #TempTable from TempDB.