日期:2014-05-18  浏览次数:21319 次

如何用C#查询sql Server 2005中是否存在“表名”表,如果不存在则使用此“表名”创建表
C# code


                    SqlConnection myCon2 = new SqlConnection();
                    myCon2.ConnectionString = "Persist Security Info=False;User id=sa;pwd=111111;database=message;server=(local)";
                    myCon2.Open();

                    Console.WriteLine("请输入员工编号:");
                    string user_id = Console.ReadLine();

                    SqlDataAdapter adp = new SqlDataAdapter();
                    DataTable dt = myCon2.GetSchema();

                    try
                    {
                        SqlCommand cmd = new SqlCommand("select * from user_id" , myCon2);
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex) {
                        switch (ex.Number) { 
                            case 1146:
                                Console.WriteLine("无此表,创建表");
                                string mySelectQuery="CREATE TABLE user_id(usermessage VARCHAR(MAX))";
                                SqlCommand cmd = new SqlCommand(mySelectQuery, myCon2);
                                cmd.ExecuteNonQuery();
                                Console.Read();
                                break;
                            default:
                                break;
                        }
                    }




我想通过输入的员工编号user_id来判定数据库中是否存在表名为user_id的表,如果没有则以输入的编号为表名创建表。可是上面的代码不能实现这个功能,请各位大侠指点迷津。谢谢

------解决方案--------------------
select count(*) from sysobjects where id = object_id("数据库名.Owner.表名") 



------解决方案--------------------
或者做成存储过程:

if not exists(select * from sysobjects where id=object_Id('dbo.user_id') )
CREATE TABLE user_id(usermessage VARCHAR(MAX))