Often you wanna make sure some tables in database exist, but you want to avoid getting the error message “There is already an object named ‘yourtable’ in the database.”, that you would get by simply issuing the CREATE TABLE command.
For these cases, MSSQL offers keyword EXISTS (most SQL engines provide similar facilities; e.g. MySQL C API allows you to directly check for the existance of database and/or table).
All you have to do in MSSQL is make sure the table is not yet registered in the sysobjects table, using the following SQL command:
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[tablename] ( columns specification );
where “tablename” is name of table you want to create, and “columns specification” is the usual definition of table’s columns. Feel free to extend the table definition to your liking.
Or you might want to DROP the table before re-creating it:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[tablename];
CREATE TABLE [dbo].[tablename] ( columns specification );
Yes, that’s all. Good luck!
Thanks for the tip, it worked quite well.
Glad to help!
I’m getting the following error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘’’.
I just copypasta’d it from this site and replaced the tablename thingy, but it doesn’t seem to work. Changed some of the quotes around, but still can’t get it to work. Any ideas?
I’m amazed by the excessive complexity of just determining if a table exists in MSSQL, =D. ‘IF NOT EXISTS table_name’ > this.
That’s a problem of wordpress, that it converts all normal quotes (single and double) to the fancy ones. You have to convert all of them to normal ones by hand, sorry.
I use this approach, think it is easier.
IF OBJECT_ID(N’[dbo].[tablename]‘, N’U') IS NULL
CREATE TABLE [dbo].[tablename] ( columns specification );
Thanks for the approach,
It helped
Vinod
There’s a bug in the original code.
You ask for NOT EXISTS which evaluates to true oif the SELECT returns FALSE, if the object IS NOT htere and then you additionally ask if that object is a user table or not.
This is contradictory.
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N’[dbo].[ch_sbt_hm1212]‘) and xtype=N’U')
–do stuff here …
select 1 as tableExists
else
select 0 as tableExists