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