获取Access用户定义的表名和各表的字段名

 public   static  DataTable GetSchemaTable(string connectionString)  //获取Access所有的表名；

        {
            using (OleDbConnection connection = new
                       OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable schemaTable = connection.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                return schemaTable;
            }
        }

 

 

 private string[] GetTabelName(string conn)  //获取Access文件中所有表名，放在数组里面
        {
            DataTable dt = GetSchemaTable(conn);
            int n = dt.Rows.Count;
            string[] strTable = new string[n];
            int m = dt.Columns.IndexOf("TABLE_NAME");
            for (int i = 0; i < n; i++)
            {
                DataRow m_dataRow = dt.Rows[i];
                strTable[i] = m_dataRow.ItemArray.GetValue(m).ToString();

            }
            return strTable;
        }

 

private string[] GetAccessTableFiled(string tableName, string conn) //获取Access指定表中的字段名
        {
            string sql = String.Format("select top 1 * from {0}", tableName);

            DataTable dt = new DataTable();
            AccessHelper.ExecuteSql(sql, out dt, conn);
            int count = dt.Columns.Count;
            string[] filed = new string[count];
            for (int i = 0; i < count; i++)
            {
                filed[i] = dt.Columns[i].ColumnName.ToString().Trim();
            }
            return filed;

        }

 

 

 

 

 

create proc getTableStruct  //获取SQLServer2000表的结构的存储过程
@tabName varchar(20)
as
SELECT    
          表名           =   case   when   a.colorder=1   then   d.name   else   ''   end,  
           
          字段序号       =   a.colorder,  
          字段名           =   a.name,  
         
          主键               =   case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   parent_obj=a.id   and   name   in   (  
                                            SELECT   name   FROM   sysindexes   WHERE   indid   in(  
                                                  SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid)))   then   '√'   else   ''   end,  
          类型               =   b.name,  
          占用字节数   =   a.length,  
          长度               =   COLUMNPROPERTY(a.id,a.name,'PRECISION')
         
FROM    
          syscolumns   a  
left   join    
          systypes   b    
on    
          a.xusertype=b.xusertype  
inner   join    
          sysobjects   d    
on    
          a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'  
left   join    
          syscomments   e    
on    
          a.cdefault=e.id  
left   join    
          sysproperties   g    
on    
          a.id=g.id   and   a.colid=g.smallid      
left   join    
          sysproperties   f    
on    
          d.id=f.id   and   f.smallid=0  
where    
          d.name in (@tabName)        --如果只查询指定表,加上此条件  
order   by    
          a.id,a.colorder