Check if table exists, before performing operations on it?

Question

In searching for an answer to this question, I found this popular post on StackOverflow. Unfortunately, it doesn't work completely. The question is this:

Is there a way to check for existence of a table (or another object) before performing modifications (e.g. INSERT)? The before mentioned post suggests this:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'questionableTable'))
BEGIN
    INSERT INTO dbo.questionableTable VALUES ('success!');
END

Error: Invalid object name 'dbo.questionableTable'.

The problem with this is that SQL Server fails when it parses the INSERT statement, stating that dbo.questionableTable doesn't exist. The previous INFORMATION_SCHEMA check doesn't seem to affect it.

Is there a way to write this kind of query? For SQL Server, in particular. But I would also like to see similar operations for other database systems, if such things exist.

The motivation behind this question is because we have multiple databases which contain subsets of each others' tables. What I would like is to have a single script that can be applied to all databases, and which only modified the tables that exist there (and doesn't error upon execution).

I cannot recreate your problem. When I try the "IF(EXISTS..." snippet it works correctly? — ron tornambe, May 18 '12 at 23:43
@rontornambe: What DB client are you using? `sqlcmd` or something else? I'm using SQL Server Management Studio 2008, on a SQL Server 2005 instance. — voithos, May 18 '12 at 23:49
@rontornambe: Ahh... I'm sorry, I messed up on the naming of the check. My mistake... this question isn't valid, but I can't delete it, so I flagged it. — voithos, May 19 '12 at 00:12
I am using Mgmt. Studio 2008 R2 on a local (2008) database instance. Perhaps you can try creating a 2008 database and retry this code. — ron tornambe, May 19 '12 at 00:13
@Kev I can't understand why you closed this question! There is nothing "localized" about the question (or my answer). It's a general problem of referring to a table in a stored proc *before* it's been created. — Bohemian, May 20 '12 at 00:59
@Bohemian - see OP's comment above "My mistake... this question isn't valid, but I can't delete it, so I flagged it." — Kev, May 20 '12 at 01:00

score 5 · Answer 1 · answered May 18 '12 at 23:43

Use dynamic SQL via the EXEC() function:

IF (EXISTS (SELECT * 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = 'dbo' 
            AND TABLE_NAME = 'questionableTable'))
BEGIN
    EXEC('INSERT INTO dbo.questionableTable VALUES (''success!'')');
END

The EXEC() function executes a string as SQL, but being a string it isn't evaluated until executed, so the tables mentioned in the string don't need to exist at compile time. This allows the stored proc to be defined prior to the table being created.

Consider using sp_executesql instead of just exec. – ulty4life May 19 '12 at 00:41 — ulty4life, May 19 '12 at 00:41

score 0 · Answer 2 · answered May 18 '12 at 23:42

0

I tested this on my local server and it seems to work:

if exists (select * from dbname.sys.tables where name='tablename')
begin
select * from dbname.dbo.tablename
end

answered May 18 '12 at 23:42

Taha Paksu

15,371
2
44
78

Did it work both with and without the table existing? – HABO May 19 '12 at 01:06
without table it returned nothing. and no errors. – Taha Paksu May 19 '12 at 01:09

Check if table exists, before performing operations on it?

2 Answers2