Check: does table exist in SQL Server database?

Question

I have an installation script containing the following:

IF NOT EXISTS(
    SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_CATALOG = 'mydatabasename' 
      AND TABLE_SCHEMA  = 'dbo' 
      AND TABLE_NAME    = 'sometablename' 
      AND TABLE_TYPE    = 'BASE TABLE'
)
CREATE TABLE dbo.sometablename ...

Problem is that

• I have to allow the user to select a different database name
• I cannot use any placeholders ("variables") in the script.
• the database server may of course contain a different database which already contains a table sometablename

The installer does a USE mydatabasename before the script is executed; and the installer allows to select a variable database name for this. However, I can't use that variable inside the script, because every replacement in the SQL script is already done at build time of the installer.

So, how can I check whether the used database already contains table sometablename?

Answer 1

Just remove TABLE_CATALOG from your query. All tables in the INFORMATION_SCHEMA.TABLES view belong to the current database.