locked
how do I check the table is exist before I create it RRS feed

  • Question

  • User311495996 posted

    I want to create a table by this code

    @{
        var db = Database.Open("StarterSite");
        var sql = "CREATE TABLE [TEST]" +
            "(ID INT IDENTITY NOT NULL PRIMARY KEY, "+
            "FIRSTNAME NVARCHAR(50) NOT NULL, "+
            "FAMILYNAME NVARCHAR(50) NOT NULL)";
        db.Execute(sql);
        Response.Write("The database table is created.");
    }

    How I check the table named "test" is already exist before I create it 

    Monday, November 21, 2016 11:36 PM

Answers

  • User-2057865890 posted

    Hi Xiemosi,

    You could use Database.QueryValue Method.

    var sql = "select count(*) from information_schema.tables where table_name ='Test'";
    var result = db.QueryValue(sql);

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 22, 2016 8:16 AM

All replies

  • User-2057865890 posted

    Hi Xiemosi,

    To check table exists, you could try below.

    select * from information_schema.tables where table_name ='Test'
    
    select count(*) from information_schema.tables where table_name ='Test'

    Best Regards,

    Chris

    Tuesday, November 22, 2016 2:00 AM
  • User311495996 posted

    Thank you Chris!

    Your statement is worked in Sql Server Management Studio!

    I changed my code like this:

        var db = Database.Open("StarterSite");
        var sql = "select count(*) from information_schema.tables where table_name ='Test'";
        int result = Convert.ToInt32(db.Execute(sql));
    
        if (result == 0)
        {
    
            var sql1 = "CREATE TABLE [TABLENAME]" +
            "(ID INT IDENTITY NOT NULL PRIMARY KEY, " +
            "FIRSTNAME NVARCHAR(50) NOT NULL, " +
            "FAMILYNAME NVARCHAR(50) NOT NULL)";
            db.Execute(sql1);
            Response.Write("The database table is created.");
        }
        else
        {
            Response.Write("The database table is exist.");
        }
    

    whether the table is exist, the result = -1;

    How I can solve this problem?

    Tuesday, November 22, 2016 6:11 AM
  • User-2057865890 posted

    Hi Xiemosi,

    You could use Database.QueryValue Method.

    var sql = "select count(*) from information_schema.tables where table_name ='Test'";
    var result = db.QueryValue(sql);

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 22, 2016 8:16 AM