获得目标服务器中所有数据库名、表名、列名

  1 　　　　　　/// <summary>
  2         /// 获得目标服务器所有数据库名
  3         /// </summary>
  4         /// <param name="serverName"></param>
  5         /// <param name="userName"></param>
  6         /// <param name="password"></param>
  7         public void getDataBaseNameList(string serverName, string userName, string password)
  8         {
  9             SQLDMO.Application sqlApplication = new SQLDMO.ApplicationClass();
 10             SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();
 11             
 12             sqlServer.Connect(serverName, userName, password);          // 连接服务器
 13 
 14             foreach (SQLDMO.Database databBase in sqlServer.Databases)
 15             {
 16                 if (databBase.Name != null)
 17                 {
 18                     this.DataBaseTreeView.Nodes.Add(databBase.Name);
 19 
 20                     getDataBaseTableList(serverName, userName, password, databBase.Name);
 21                 }
 22             }
 23         }
 24 
 25 
 26         /// <summary>
 27         /// 加载数据库中表
 28         /// </summary>
 29         /// <param name="serverName">服务器名</param>
 30         /// <param name="userName">用户名</param>
 31         /// <param name="password">密码</param>
 32         /// <param name="dataBaseName">数据库名</param>
 33         private void getDataBaseTableList(string serverName, string userName, string password, string dataBaseName)
 34         {
 35             SQLDMO.SQLServer Server = new SQLDMO.SQLServerClass();
 36 
 37             //连接到服务器 
 38             Server.Connect(serverName, userName, password);
 39 
 40             //对所有的数据库遍历，获得指定数据库 
 41             for (int i = 0; i < Server.Databases.Count; i++)
 42             {
 43                 //判断当前数据库是否是指定数据库 
 44                 if (Server.Databases.Item(i + 1, "dbo").Name == dataBaseName)
 45                 {
 46                     //获得指定数据库 
 47                     SQLDMO._Database db = Server.Databases.Item(i + 1, "dbo");
 48 
 49                     //获得指定数据库中的所有表 
 50                     for (int j = 0; j < db.Tables.Count; j++)
 51                     {
 52                         this.DataBaseTreeView.Nodes[i].Nodes.Add(db.Tables.Item(j + 1, "dbo").Name);
 53                     }
 54                 }
 55             }
 56         }
 57 
 58 
 59     /// <summary>
 60         /// 获得表中所有列名
 61         /// </summary>
 62         /// <param name="serverName">服务器名</param>
 63         /// <param name="userName">用户名</param>
 64         /// <param name="password">密码</param>
 65         /// <param name="tableName">表名</param>
 66         /// <param name="dataBaseName">数据库名</param>
 67         /// <returns></returns>
 68         public string getRowListFromTable(string serverName, string userName, string password, string tableName, string dataBaseName)
 69         {
 70             string result = string.Empty;
 71 
 72             string connectionString = string.Empty;
 73             connectionString += "server=" + serverName;
 74             connectionString += ";Pwd=" + password;
 75             connectionString += ";UID=" + userName;
 76             connectionString += ";Database=" + dataBaseName;
 77 
 78             string commandString = string.Empty;
 79             commandString += "select   name   from   syscolumns   where   id=object_id('";
 80             commandString += tableName;
 81             commandString += "')";
 82             
 83 
 84             SqlConnection sqlConnection = new SqlConnection(connectionString);
 85             SqlCommand sqlCommand = new SqlCommand(commandString, sqlConnection);
 86 
 87             SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, sqlConnection);
 88 
 89             DataSet dataSet = new DataSet();
 90             dataAdapter.Fill(dataSet);
 91 
 92             DataTable dataTable = dataSet.Tables[0];
 93 
 94             // DataTable dataTable = sqlConnection.GetSchema("Tables");
 95             foreach (DataRow row in dataTable.Rows)
 96             {
 97                 result += row[0].ToString() + "-";
 98             }
 99 
100             if (result != null)
101             {
102                 return result;  
103             }
104             else
105             {
106                 return "0";
107             }
108         }

 下面是转载的代码：

       using System.Data.SqlClient;

        /// <summary>
        /// 获取局域网内的所有数据库服务器名称
        /// </summary>
        /// <returns>服务器名称数组</returns>
        public List<string> GetSqlServerNames()
        {
            DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();

            DataColumn column = dataSources.Columns["InstanceName"];
            DataColumn column2 = dataSources.Columns["ServerName"];

            DataRowCollection rows = dataSources.Rows;
            List<string> Serverlist = new List<string>();
            string array = string.Empty;
            for (int i = 0; i < rows.Count; i++)
            {
                string str2 = rows[i][column2] as string;
                string str = rows[i][column] as string;
                if (((str == null) || (str.Length == 0)) || ("MSSQLSERVER" == str))
                {
                    array = str2;
                }
                else
                {
                    array = str2 + @"/" + str;
                }

                Serverlist.Add(array);
            }

            Serverlist.Sort();

            return Serverlist;
        }

        /// <summary>
        /// 查询sql中的非系统库
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<string> databaseList(string connection)
        {
            List<string> getCataList = new List<string>();
            string cmdStirng = "select name from sys.databases where database_id > 4";
            SqlConnection connect = new SqlConnection(connection);
            SqlCommand cmd = new SqlCommand(cmdStirng, connect);
            try
            {
                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                    IDataReader dr = cmd.ExecuteReader();
                    getCataList.Clear();
                    while (dr.Read())
                    {
                        getCataList.Add(dr["name"].ToString());
                    }
                    dr.Close();
                }

            }
            catch (SqlException e)
            {
                //MessageBox.Show(e.Message);
            }
            finally
            {
                if (connect != null && connect.State == ConnectionState.Open)
                {
                    connect.Dispose();
                }
            }
            return getCataList;
        }

        /// <summary>
        /// 获取列名
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<string> GetTables(string connection)
        {
            List<string> tablelist = new List<string>();
            SqlConnection objConnetion = new SqlConnection(connection);
            try
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                    DataTable objTable = objConnetion.GetSchema("Tables");
                    foreach (DataRow row in objTable.Rows)
                    {
                        tablelist.Add(row[2].ToString());
                    }
                }
            }
            catch
            {

            }
            finally
            {
                if (objConnetion != null && objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Dispose();
                }

            }
            return tablelist;
        }

        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> GetColumnField(string connection, string TableName)
        {
            List<string> Columnlist = new List<string>();
            SqlConnection objConnetion = new SqlConnection(connection);
            try
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }

                SqlCommand cmd = new SqlCommand("Select Name FROM SysColumns Where id=Object_Id('" + TableName + "')", objConnetion);
                SqlDataReader objReader = cmd.ExecuteReader();

                while (objReader.Read())
                {
                    Columnlist.Add(objReader[0].ToString());

                }
            }
            catch
            {

            }
            objConnetion.Close();
            return Columnlist;
        }

　　2：

region   得到所有本地网络中可使用的SQL服务器列表 
///   <summary> 
///   得到所有本地网络中可使用的SQL服务器列表 
///   </summary> 
///   <param   name= "p_strServerList "> 服务器列表 </param> 
///   <returns> </returns> 
public   static   bool   GetServers(ref   string   []   p_strServerList) 
{ 
       try 
      { 
            SQLDMO.Application   sqlApp   =   new   SQLDMO.ApplicationClass();   
            SQLDMO.NameList   sqlServers   =   sqlApp.ListAvailableSQLServers();   
            if(sqlServers.Count   >   0) 
            { 
                 p_strServerList   =   new   string[sqlServers.Count]; 
                 for(int   i=0;i <sqlServers.Count;i++)   
                {   
                    string   srv   =   sqlServers.Item(i   +   1);   
                    if(srv   !=   null)   
                   {   
                       p_strServerList[i]   =   srv;                                                   
                   }   
               }   
           } 
          return   true; 
     } 
     catch(Exception   ex) 
     { 
          throw   ex; 
     } 
} 

#endregion 

#region   得到指定SQL服务器所有数据库的列表 
///   <summary> 
///   得到指定SQL服务器所有数据库的列表 
///   </summary> 
///   <param   name= "p_strDataBaseList "> 数据库列表 </param> 
///   <param   name= "p_strServer "> 服务器名 </param> 
///   <param   name= "p_strUser "> 用户名 </param> 
///   <param   name= "p_strPWD "> 密码 </param> 
///   <returns> </returns> 
public   static   bool   GetDataBases(ref   string   []   p_strDataBaseList,   string   p_strServer,   string   p_strUser,   string   p_strPWD) 
{ 
      try 
     { 
          int   i   =   0; 

          SQLDMO.Application   sqlApp   =   new   SQLDMO.ApplicationClass();   
          SQLDMO.SQLServer   srv   =   new   SQLDMO.SQLServerClass();                                   
          srv.Connect(p_strServer,p_strUser,p_strPWD);   

          if(srv.Databases.Count   >   0) 
         { 
             p_strDataBaseList   =   new   string[srv.Databases.Count]; 

             foreach(SQLDMO.Database   db   in   srv.Databases)   
            {   
                 if(db.Name!=null)   
                { 
                     p_strDataBaseList[i]   =   db.Name; 
                } 
                i   =   i   +   1; 
           } 
        } 
        return   true; 
   } 
   catch(Exception   ex) 
   { 
         throw   ex; 
    } 
} 

#endregion 

#region   得到所有的存储过程 
///   <summary> 
///   得到所有的存储过程 
///   </summary> 
///   <param   name= "p_strProcedureList "> 存储过程列表 </param> 
///   <param   name= "p_strServer "> 服务器名 </param> 
///   <param   name= "p_strUser "> 用户名 </param> 
///   <param   name= "p_strPWD "> 密码 </param> 
///   <param   name= "p_strDataBase "> 数据库名 </param> 
///   <returns> </returns> 
public   static   bool   GetProcedures(ref   string   []   p_strProcedureList,   string   p_strServer,   string   p_strUser,   string   p_strPWD,   string   p_strDataBase) 
{ 
       try 
      { 
            SQLDMO.SQLServer   srv   =   new   SQLDMO.SQLServerClass();                                   
            srv.Connect(p_strServer,p_strUser,p_strPWD);   

            for(int   i=0;i <srv.Databases.Count;i++)   
           {   
                if(srv.Databases.Item(i+1, "dbo ").Name   ==   p_strDataBase)   
                {   
                     SQLDMO._Database   db=   srv.Databases.Item(i+1, "dbo ");   
                     if   (db.StoredProcedures.Count   >   0) 
                    { 
                         p_strProcedureList   =   new   string[db.StoredProcedures.Count]; 

                         for(int   j=0;j <db.StoredProcedures.Count;j++)   
                         {   
                             p_strProcedureList[j]   =   db.StoredProcedures.Item(j+1, "dbo ").Name;   
                         }   
                         break;   
                    }   
              } 
         } 

        return   true; 
   } 
   catch(Exception   ex) 
   { 
        throw   ex; 
    } 
} 
#endregion 

#region   得到所有的Tables集合 
///   <summary> 
///   得到所有的Tables集合 
///   </summary> 
///   <param   name= "p_strProcedureList "> Tables集合 </param> 
///   <param   name= "p_strServer "> 服务器名 </param> 
///   <param   name= "p_strUser "> 用户名 </param> 
///   <param   name= "p_strPWD "> 密码 </param> 
///   <param   name= "p_strDataBase "> 数据库名 </param> 
///   <returns> </returns> 
public   static   bool   GetTables(ref   string   []   p_strTableList,   string   p_strServer,   string   p_strUser,   string   p_strPWD,   string   p_strDataBase) 
{ 
      try 
      { 
            SQLDMO.SQLServer   srv   =   new   SQLDMO.SQLServerClass();                                   
            srv.Connect(p_strServer,p_strUser,p_strPWD);   

            for(int   i=0;i <srv.Databases.Count;i++)   
            {   
                 if(srv.Databases.Item(i+1, "dbo ").Name   ==   p_strDataBase)   
                 {   
                      SQLDMO._Database   db=   srv.Databases.Item(i+1, "dbo ");   
                      if   (db.Tables.Count   >   0) 
                      { 
                           p_strTableList   =   new   string[db.Tables.Count]; 

                           for(int   j=0;j <db.Tables.Count;j++)   
                          {   
                               p_strTableList[j]   =   db.Tables.Item(j+1, "dbo ").Name;   
                          }   
                          break;   
                     }   
               } 
         } 

         return   true; 
     } 
     catch(Exception   ex) 
     { 
         throw   ex; 
     } 
} 
#endregion 

#region   得到所有的Views集合 
///   <summary> 
///   得到所有的Views集合 
///   </summary> 
///   <param   name= "p_strProcedureList "> Views集合 </param> 
///   <param   name= "p_strServer "> 服务器名 </param> 
///   <param   name= "p_strUser "> 用户名 </param> 
///   <param   name= "p_strPWD "> 密码 </param> 
///   <param   name= "p_strDataBase "> 数据库名 </param> 
///   <returns> </returns> 
public   static   bool   GetViews(ref   string   []   p_strViewList,   string   p_strServer,   string   p_strUser,   string   p_strPWD,   string   p_strDataBase) 
{ 
     try 
     { 
          SQLDMO.SQLServer   srv   =   new   SQLDMO.SQLServerClass();                                   
          srv.Connect(p_strServer,p_strUser,p_strPWD);   

          for(int   i=0;i <srv.Databases.Count;i++)   
         {   
              if(srv.Databases.Item(i+1, "dbo ").Name   ==   p_strDataBase)   
              {   
                   SQLDMO._Database   db=   srv.Databases.Item(i+1, "dbo ");   
                   if   (db.Views.Count   >   0) 
                   { 
                        p_strViewList   =   new   string[db.Views.Count]; 

                        for(int   j=0;j <db.Views.Count;j++)   
                        {   
                             p_strViewList[j]   =   db.Views.Item(j+1, "dbo ").Name;   
                        }   
                        break;   
                  }   
           } 
      } 

      return   true; 
   } 
   catch(Exception   ex) 
   { 
        throw   ex; 
    } 
} 
#endregion

　　

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/iwenr/p/3715630.html