CREATE TABLE fails Table already exists but it does not

[thendrickson]

This has probably been answered befor, but I have a create table statement failing.

I check for IF exists in sysobjects then drop the twtable and try to create a new table.

I receive the error that the table already exists.

This is the code I have always used

IF EXISTS
( SELECT *
FROM dbo.sysobjects
WHERE id = object_id( N'[tmpWhatever]' )
AND ObjectProperty( id, N'IsUserTable') = 1 )
DROP TABLE tmpWHatever
CREATE TABLE [tmpWhatever] ( ETC, ETC ETC

The create generates the error that the table already exists.

THe confusing part is that if I use this code to debug. The table is gone after the drop but the Create still fails,

IF EXISTS
( SELECT *
FROM dbo.sysobjects
WHERE id = object_id( N'[tmpWhatever]' )
AND ObjectProperty( id, N'IsUserTable') = 1 )
Begin
Print 'Dropping it'
DROP TABLE tmpWHatever
end

IF EXISTS
( SELECT *
FROM dbo.sysobjects
WHERE id = object_id( N'[tmpWhatever]' )
AND ObjectProperty( id, N'IsUserTable') = 1 )
else
Print 'tbl is gone'

So the table is not in sysobjects at this point, but my CREATE TABLE still fails even though it isthe very next code to fire.

Any ideas would be welcome

 

[gmmastros]

Instead of using the system tables, I suggest you use the information_schema views.

IF EXISTS
   ( SELECT * 
     FROM 	Information_Schema.tables
     WHERE 	Table_Name = 'tmpWhatever'
	)
    Begin
    Print 'Dropping it'
    DROP TABLE tmpWHatever 
    end

[green]-- If the table already existed, we just dropped it, 
-- so we are safe creating it.[/green]
Create table tmpWhatever(Col1 Integer, Col2 VarChar(10))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Are you checking for a temp table?

if so use this

IF OBJECT_ID('tempdb..#tmpWhatever','u') IS NOT NULL
BEGIN
PRINT '#tmpWhatever exists!'
END
ELSE
BEGIN
PRINT '#tmpWhatever does not exist!'
END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> Are you checking for a temp table?

My thoughts exactly ([tmpWhatever]).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[thendrickson]

For client reasons (not my choice), I am creating a user table named tmp*****, not an actual temp table such s #tmpWhatever.

I guess that must have been confusing.

The thing is that the table does not exist after being dropped as near as I can determine so I should be able to Create a table with no problems.

Origionally the actual plan was to do an
IF EXISTS...........
TRUNCATE TABLE .....
ELSE
CREATE TABLE........

However, I was receiving the Table already exists error so I explicitly dropped the table before attempting to create the table in a futile attempt

Also please note that I do not have proper permissions to use the debugger. I am a contractor working with a client who has a DBA who is very concerned for security.

However, after Dropping the table I performed another IF Exists to see if the tmptable was gone (it was) then tried to recreate it. This attempt also failed with the table already xists error.

I am working in a Development DATABASE that I do have permissions to DROP a table in.

Oh yes, I am using SQL 2000.

 

[vongrunt]

What happens if you change logic a little bit:

IF NOT EXISTS()
BEGIN
CREATE TABLE...
END

TRUNCATE TABLE...

?


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

Maybe the problem is with table ownership.

Extending my original example may help

IF EXISTS
   ( SELECT * 
     FROM     Information_Schema.tables
     WHERE     Table_Name = 'tmpWhatever'
               And TABLE_SCHEMA = '[!]LoginName[/!]'
    )
    Begin
    Print 'Dropping it'
    DROP TABLE [[!]LoginName[/!]].tmpWHatever 
    end

-- If the table already existed, we just dropped it, 
-- so we are safe creating it.
Create table [[!]LoginName[/!]].tmpWhatever(Col1 Integer, Col2 VarChar(10))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should mention that [!]LoginName[/!] is usually [!]dbo[/!].

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[thendrickson]

Thanks I am on my way back into the clints site this evening and will try Van Grunts suggestion

I do not think it is table ownership though. the tmpWhatever table is created by each individual user then deleted when the sp is complete. In this case table ownership is not dbo, but insead is the individual user that created the table origionally.

 

[thendrickson]

Sorry it has taken me so long to post the solution to the problem.

I am modifying existing code and did not realize that there was a Select Into later in the 'procedure' trying to create a new table.

The client had a "template" of SQL Statements that were cut and pasted into QA then executed as needed. The 'SP" I was modifying was never actually executed in the first place.

This problem just reenforced something I was becoming careless about. NEVER try to modify any code until you FULLY UNDERSTAND it!

Once I reached a sufficient level of frustration, I formatted the code properly and the problem jumped up and smacked me in the face.

So my 2 cents to anybody is to never forget the basics!