Check if $tablename exists in SQL Server 2017, CREATE TABLE $tablename if it doesn't exist

Question

I would like to check if the SQL Server 2017 table $tablename (entered by the user in a PHP form) exists:

    try {
        $dothis = "
            IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N$tablename)
               CREATE TABLE $tablename ( id int IDENTITY (1,1), Name text, GeomCol1 geometry, GeomCol2 AS GeomCol1.STAsText() )";
        $tbl = $pdo->exec($dothis);
    } catch(PDOException $e) {
        echo "Error: ".$e->getMessage();
    }

But I always get the same error:

SQLSTATE[42S22]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'NNewTable'.

Here too:

    try {
        $dothis = "
            IF OBJECT_ID ( $tablename, 'U' ) IS NULL 
            BEGIN 
               CREATE TABLE $tablename ( id int IDENTITY (1,1), Name text, GeomCol1 geometry, GeomCol2 AS GeomCol1.STAsText() )
            END";
        $tbl = $pdo->exec($dothis);

Error:

SQLSTATE[42S22]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'NewTable'.

No problem creating the table if I don't check to see if the table exists first:

    try {
        $dothis = "
               CREATE TABLE $tablename ( id int IDENTITY (1,1), Name text, GeomCol1 geometry, GeomCol2 AS GeomCol1.STAsText() )";
        $tbl = $pdo->exec($dothis);
    } catch(PDOException $e) {
        echo "Error: ".$e->getMessage();
    }

No error, and it creates the table with name $tablename

I see that there are many different ways to check if a table exists in SQL Server

Unfortunately, each time I try to use a $tablename variable to check if the table exists, it returns an error. I hope someone can help.

Enclose the table name in single quotes for literals `WHERE TABLE_NAME = N'$tablename'`, `IF OBJECT_ID ( N'$tablename', 'U' ) IS NULL`. — Dan Guzman, Dec 14 '19 at 14:03

score 2 · Accepted Answer · answered Dec 14 '19 at 14:12

Enclose the table name in single quotes when used as a literal. Also, consider enclosing the table name in square brackets (or double quotes) if the name might not conform to rules for regular identifiers (e.g. embedded spaces).

try {
    $dothis = "
        IF OBJECT_ID ( N'$tablename', 'U' ) IS NULL 
        BEGIN 
           CREATE TABLE [$tablename] ( id int IDENTITY (1,1), Name text, GeomCol1 geometry, GeomCol2 AS GeomCol1.STAsText() )
        END";
    $tbl = $pdo->exec($dothis);

New to PHP, enclosing a variable name in quotes just feels wrong. But that's what you gotta do. — Rayner, Dec 14 '19 at 14:15

score 0 · Answer 2 · answered Dec 14 '19 at 14:17

This also works:

    try {
        $dothis = "
            if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = N'$tablename')
            BEGIN 
               CREATE TABLE $tablename ( id int IDENTITY (1,1), Name text, GeomCol1 geometry, GeomCol2 AS GeomCol1.STAsText() )
            END";
        $tbl = $pdo->exec($dothis);

Check if $tablename exists in SQL Server 2017, CREATE TABLE $tablename if it doesn't exist

2 Answers2