1

I have a lot of code I am trying to run where I'm querying the sysobjects table to check if an object exists before I drop it and create it again.

Issue being, sometimes if I go:

if not exists (select name from sysobjects o where o.name = 'my_table' and o.type =  'U') 
CREATE TABLE my_table (..)
go

it works, no worries. However, when I came back to run it again, I get this lovely error: 

SQL Server Error on (myserver) Error:2714 at Line:10 Message:There is already an object named 'my_table' in the database.

Thanks for that, SQL Programmer. I actually asked for you not to create this table if it already exists. -_-

Any ideas?

glasnt
  • 2,865
  • 5
  • 35
  • 55
  • your question is confusing. You ask about dropping the table, but your example shows create table if not exist. 14 years apart.. – ACV Apr 06 '23 at 18:56

3 Answers3

0

The sybase parsers object validation pass is global and not based on conditional evaluation. Even though your code can not execute CREATE TABLE the statement is still checked for syntax and applicability which fails when the system sees that the table already exists.

The only way around this that I know of is to put your create statements inside of an EXEC() which would be evaluated only if the section was executed.

Einstein
  • 4,450
  • 1
  • 23
  • 20
0

the logic to what you are doing doesn't seem quite right. based on your statement:

"I am trying to run where I'm querying the sysobjects table to check if an object exists before I drop it and create it again"

you should simply do a delete followed by a create. This way is usually better because it ensures that the table will be updated. if the table existed and you had changes, you are probably not getting what you want.

The immediate issue you are running into is an assumed db ownership that was not consistent between runs.

based on your clarification below - here is what you can do:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XXXX]') AND type in (N'U')) DROP TABLE [dbo].[XXXX] GO

CREATE TABLE [dbo].[XXXX(... GO

you can run this over and over again...

mson
  • 7,762
  • 6
  • 40
  • 70
  • I've tried both the "Please remove your previous version if you have one, then install this version" and "Please only install this version if you haven't a previous one". I just want a script that I can run again and again (for testing) without breaking anything along the way (multiple system codes, system errors trying to duplicate constraints, etc) – glasnt Jun 17 '09 at 07:21
  • your logic was wrong. the if exists should be used to drop not create. – mson Jun 17 '09 at 09:52
  • The 'If Exists' is used to drop, then create. Otherwise, it just creates it. My logic is not wrong. – glasnt Jun 18 '09 at 00:01
  • For reference, the same question was asked here, with my logic. I'm going with this answer provided: http://stackoverflow.com/questions/307942/how-do-i-conditionally-create-a-table-in-sybase-tsql/307991#307991 – glasnt Jun 18 '09 at 01:42
  • the code snippet you had up at the top did not do the delete. it looked like it did a create only if the table did not exist. the logic in your head was probably right... the logic you documented was slightly off. – mson Jun 18 '09 at 22:14
  • Originally I was wanting to only create tables if they didn't exist yet, or completely remove procedures and views and recreate them. This was so I wasn't accidently removing massive amounts of already created data. However, I found all the tables I was required to add didn't reflect this. I apologise for my misunderstanding. It's ok now, the code compiles and is now called in a nice little shell script on a linux box somewhere in the ether.. living the happy life. – glasnt Jun 19 '09 at 05:34
  • :D `you should simply do a delete followed by a create` that's a joke right? Who needs data anyway – ACV Apr 06 '23 at 18:54
0

yes, the entire batch of SQL is normalized and compiled so as to create an "execution plan" for the entire batch. During normalization, the "possible" "create table" statement is a problem if it already exists at compile time.

My solution: rename -

if exists (select 1 from ....) begin drop table xyz create table xyz_zzzz ( ... ) exec sp_rename 'xyz_zzzz','xyz' end