none
How to check if a SQL Server 2008 R2 table exists by using C#

    問題

  • Hi, any idea on how to check if a SQL table exists through C#?  Is there a command query I can execute to find out if the table exists?  Thanks for the help in advance.
    2011年7月13日 下午 12:14

解答

  • So I figured this out for the most part.  I just added this query, it works fine:

     

     

          using (SqlConnection myConnection = new SqlConnection("Server=cmiclosed2;Database=" + Database + ";Trusted_Connection=True;"))
          {
           myConnection.Open();
    
           StringBuilder exists = new StringBuilder("IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id('[dbo].[" + SQLTableName + "]'))"
            + " drop table [dbo].[" + SQLTableName + "]");
    
           string sql2 = exists.ToString();
           SqlCommand cmd2 = new SqlCommand(sql2, myConnection);
           cmd2.ExecuteNonQuery();
          }
    

     Where database is the name of the database in SQL and the SQLTableName is the name of the SQL table.


    • 已標示為解答 nkarek23 2011年7月13日 下午 02:43
    2011年7月13日 下午 02:43

所有回覆

  • So I figured this out for the most part.  I just added this query, it works fine:

     

     

          using (SqlConnection myConnection = new SqlConnection("Server=cmiclosed2;Database=" + Database + ";Trusted_Connection=True;"))
          {
           myConnection.Open();
    
           StringBuilder exists = new StringBuilder("IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id('[dbo].[" + SQLTableName + "]'))"
            + " drop table [dbo].[" + SQLTableName + "]");
    
           string sql2 = exists.ToString();
           SqlCommand cmd2 = new SqlCommand(sql2, myConnection);
           cmd2.ExecuteNonQuery();
          }
    

     Where database is the name of the database in SQL and the SQLTableName is the name of the SQL table.


    • 已標示為解答 nkarek23 2011年7月13日 下午 02:43
    2011年7月13日 下午 02:43
  • If you're using C#, try this

    private bool TableExists(string tableName, SqlConnection cnn)

    {

        DataTable dt = cnn.GetSchema("Tables");

        dt.PrimaryKey = new DataColumn[] { dt.Columns["Table_Name"] };

        return (dt.Rows.Find(tableName != null));

    }

    If you could have the same table name in different schemas, add "Schema" to the primary key.


    John Carroll


    • 已編輯 llorrac 2012年2月23日 下午 03:07
    2012年2月23日 下午 03:07