Check if table name exists SQL

Question

How can I check if a table already exists before creating a new one?

Updated Code:

    private void checkTable()
            {

                string tableName = quotenameTxt.Text + "_" + firstTxt.Text + "_" + surenameTxt.Text;
                string connStr = @"Data Source=|DataDirectory|\LWADataBase.sdf";
             //   SqlCeConnection conn = new SqlCeConnection(connStr);
            //    if (conn.State == ConnectionState.Closed) { conn.Open(); }
                using (SqlCeConnection conn = new SqlCeConnection(connStr))
    {
        conn.Open();    
        SqlCeCommand cmd = new SqlCeCommand(@"SELECT * 
                                              FROM INFORMATION_SCHEMA.TABLES 
                                              WHERE TABLE_NAME = @tname", conn);
        cmd.Parameters.AddWithValue("@tname", tableName);
        SqlCeDataReader reader = cmd.ExecuteReader();
        if(reader.Read()){
            MessageBox.Show("Table exists");}
        else{
            MessageBox.Show("Table doesn't exist");
createtable();}

possible duplicate of [SQL Server: Check if table exists](http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists) — ebo, Jun 19 '14 at 17:55
possible duplicate of [Can I get name of all tables of SQL Server database in C# application?](http://stackoverflow.com/questions/3005095/can-i-get-name-of-all-tables-of-sql-server-database-in-c-sharp-application) — Dour High Arch, Jun 19 '14 at 18:21

Steve · Accepted Answer · 2014-06-22T09:46:34.367

1

Sql Server Compact supports the INFORMATION_SCHEMA views

using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
    conn.Open();    
    SqlCeCommand cmd = new SqlCeCommand(@"SELECT TOP 1 * 
                                          FROM INFORMATION_SCHEMA.TABLES 
                                          WHERE TABLE_NAME = @tname", conn);
    cmd.Parameters.AddWithValue("@tname", tableName)
    SqlCeDataReader reader = cmd.ExecuteReader();
    if(reader.Read())
        Console.WriteLine("Table exists");
    else
        Console.WriteLine("Table doesn't exist");

}

EDIT In version 3.5 it seems that the TOP 1 instruction is not accepted. However, given the WHERE clause it should make no difference using it or not so, to make it work just change the query to

SqlCeCommand cmd = new SqlCeCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES 
                                      WHERE TABLE_NAME = @tname", conn);

SECOND EDIT Looking at the code that creates the table.
(It is In chat, I suggest to add it to the question for completeness)

using (SqlCeCommand command = new SqlCeCommand( 
        "CREATE TABLE ['" + tableName + "'] " + 
        "(Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))

The single quotes around the tableName variables becomes part of the name of the table. But the check for table exists doesn't use the quotes. And your code fall through the path that tries to create again the table with the quotes. Just remove the quotes around the name. They are not needed.

edited Jun 22 '14 at 09:46

answered Jun 19 '14 at 17:58

Steve

213,761
22
232
286

There was an error parsing the query. [ Token line number = 1,Token line offset = 12,Token in error = 1 ] is the error i get from this code – Tom Jun 19 '14 at 18:07
I have no error testing with a database with Sql Server Compact 4.0 – Steve Jun 19 '14 at 19:57
Well, I cannot test with 3.5 now. But try to remove the `TOP 1` and leave only `SELECT * FROM INFORMATION_SCHEMA.TABLES ....` this should be absolutely correct unless the INFORMATION_SCHEMA views are not supported in 3.5. – Steve Jun 19 '14 at 21:05
Ok just downloaded and tested with the 3.5 bits. It is the TOP 1. Remove it and it should work fine – Steve Jun 19 '14 at 21:14
I've done as you said but its not actually doing anything. Regardless of whether the table exists it just gives me the "table does not exist" messagebox. – Tom Jun 20 '14 at 17:54
Consider that |DataDirectory| substitution string resolves in \bin\debug in a winforms application. Are you checking against the correct database? Are you using the parameter as shown in my example or are you using the string concatenation as you have done in your code. – Steve Jun 20 '14 at 19:00
Well, I cannot find any reason for this code to not work (of course if the table really exists). Remember that this works on the database present in your BIN\DEBUG or BIN\x86\DEBUG folder, not on the database in the project folder. – Steve Jun 21 '14 at 18:36
I know, but for existing tables, those that are already in the database (created from the server explorer function) it isn't saying they exist – Tom Jun 21 '14 at 19:01
What are these names not found? An example of an existing name could help – Steve Jun 21 '14 at 19:21
customersTBL (created in the server explorer) – Tom Jun 21 '14 at 19:27
Ahh! I forgot im using a string, so the table called customersTBL does exist, but the table called "customersTBL_example_text" does. Schoolboy error. – Tom Jun 21 '14 at 19:42
Now its not recognising that I'm creating new tables (create a table, exit debug, enter debug, enter the same details and it says it doesn't exist, then gives me an error in the createtable class (called in if the table doesnt exist) saying the table exists. – Tom Jun 21 '14 at 19:46
I am lost. Please if the original question is resolved then I suggest to choose an answer as the accepted one and then post a new question with the new problem. – Steve Jun 21 '14 at 19:49
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56037/discussion-between-user3521452-and-steve). – Tom Jun 21 '14 at 19:56
Your problem is the "CREATE TABLE ['" + tablename + "'] ..... ". In this way if you have tablename = "MyTable" you create a table with the name 'MyTable' with the quotes that are part of the name. Then you test for the table without the quotes – Steve Jun 21 '14 at 21:09
i believe that has cured it. Thanks @Steve – Tom Jun 22 '14 at 20:27

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

You can use the SqlClientConnection to get list of all objects in the db.

private void checkTable()
{
    string tableName = quotenameTxt.Text + "-" + firstTxt.Text + "-" + surenameTxt.Text;
    string connStr = @"Data Source=|DataDirectory|\LWADataBase.sdf";

    using (SqlCeConnection conn = new SqlCeConnection(connStr))
    {
        bool isTableExist = conn.GetSchema("Tables")
                                .AsEnumerable()
                                .Any(row => row[2] == tableName);
    }

    if (!isTableExist)
    {
        MessageBox.Show("No such data table exists!");
    }
    else
    {
        MessageBox.Show("Such data table exists!");
    }
}

Source: https://stackoverflow.com/a/3005157/1271037

edited May 23 '17 at 12:27

Community

1
1

answered Jun 19 '14 at 17:58

dovid

6,354
3
33
73

"Specified method is not supported" – Tom Jun 19 '14 at 20:48
1

GetSchema added in version 4.0 http://technet.microsoft.com/en-us/library/ms172417(v=sql.110).aspx – Steve Jun 19 '14 at 21:17

Check if table name exists SQL

2 Answers2