如果是SQL SERVER,可以用: select * from sysobjects where xtype='U' 取得所有用户表
in sql server: select distinct table_name from columns in oracle: select * from tab in db2: select * from syscat.tables in access: select * from mssysobjects
【select * from user_tab_columns】能查出当前用户下的所有表名、字段名以及数据类型、长度等等信息。--Oracle 【select object_name from user_objects where object_type='table'】查出当前用户下的所有表名--Oracle 【select name from sysobjects where xtype='u'】查出当前所在数据库的所有表名--SqlServer
这是【saucer(思归)】前面回答过的帖子: 1. use classes in System.Data.SqlClient, connect to the "master" database, do a query "select name from sysdatabases" to get the list of databases, then change connection to each database, do a query "select name from sysobjects where type='U'" to get the list of tablesusing System; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Data.OleDb;string ConnectionString = "Server=localhost;Database=master;UID=sa;PWD=;"; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand("select name from sysdatabases", conn); conn.Open(); ArrayList a = new ArrayList(); SqlDataReader dr = cmd.ExecuteReader(); Console.WriteLine("list of databases:"); while (dr.Read()) { a.Add((String)dr["name"]); Console.WriteLine(dr["name"]); } dr.Close(); cmd.CommandText = "select name from sysobjects where type='U''"; foreach (String Database in a) { Console.WriteLine("*****list of tables in database {0}:*********", Database); conn.ChangeDatabase(Database); dr = cmd.ExecuteReader(); while(dr.Read()) { Console.WriteLine(dr["name"]); } dr.Close(); } conn.Close();2. use OleDbConnection's GetOleDbSchemaTable method:string ConnectionString = "Provider=SQLOLEDB;Server=localhost;Database=master;UID=sa;PWD=;"; OleDbConnection conn = new OleDbConnection(ConnectionString); conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, new object[] {null}); ArrayList a = new ArrayList(); Console.WriteLine("list of databases:"); foreach (DataRow dr in schemaTable.Rows) { a.Add((String)dr["CATALOG_NAME"]); Console.WriteLine(dr["CATALOG_NAME"]); } foreach (String Database in a) { conn.ChangeDatabase(Database); Console.WriteLine("*****list of tables in database {0}:*********", Database); schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"}); foreach (DataRow dr in schemaTable.Rows) Console.WriteLine(dr["TABLE_NAME"]); } conn.Close();
select * from sysobjects where xtype='U'
这个代码包括Sql,Foxpro,Excel,Access,paradox using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.Common;namespace XmlToDatabase { /// <summary> /// DToXml 的摘要说明。 /// </summary> public class XmlToD {
private string n_XmlFileName="tmp.xml"; public string XmlFileName { set { n_XmlFileName=value; } get { return n_XmlFileName; } } public XmlToD() { // // TODO: 在此处添加构造函数逻辑 // } public DataSet XmlToSql(string strConn) {
select * from sysobjects where xtype='U' 取得所有用户表
select distinct table_name from columns
in oracle:
select * from tab
in db2:
select * from syscat.tables
in access:
select * from mssysobjects
【select object_name from user_objects where object_type='table'】查出当前用户下的所有表名--Oracle
【select name from sysobjects where xtype='u'】查出当前所在数据库的所有表名--SqlServer
1. use classes in System.Data.SqlClient, connect to the "master" database, do a query "select name from sysdatabases" to get the list of databases, then change connection to each database, do a query "select name from sysobjects where type='U'" to get the list of tablesusing System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;string ConnectionString = "Server=localhost;Database=master;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("select name from sysdatabases", conn);
conn.Open();
ArrayList a = new ArrayList();
SqlDataReader dr = cmd.ExecuteReader();
Console.WriteLine("list of databases:");
while (dr.Read())
{
a.Add((String)dr["name"]);
Console.WriteLine(dr["name"]);
}
dr.Close();
cmd.CommandText = "select name from sysobjects where type='U''";
foreach (String Database in a)
{
Console.WriteLine("*****list of tables in database {0}:*********", Database);
conn.ChangeDatabase(Database);
dr = cmd.ExecuteReader();
while(dr.Read())
{
Console.WriteLine(dr["name"]);
}
dr.Close();
}
conn.Close();2. use OleDbConnection's GetOleDbSchemaTable method:string ConnectionString = "Provider=SQLOLEDB;Server=localhost;Database=master;UID=sa;PWD=;";
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs,
new object[] {null});
ArrayList a = new ArrayList();
Console.WriteLine("list of databases:");
foreach (DataRow dr in schemaTable.Rows)
{
a.Add((String)dr["CATALOG_NAME"]);
Console.WriteLine(dr["CATALOG_NAME"]);
}
foreach (String Database in a)
{
conn.ChangeDatabase(Database);
Console.WriteLine("*****list of tables in database {0}:*********", Database);
schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"}); foreach (DataRow dr in schemaTable.Rows)
Console.WriteLine(dr["TABLE_NAME"]);
}
conn.Close();
这个代码包括Sql,Foxpro,Excel,Access,paradox
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Common;namespace XmlToDatabase
{
/// <summary>
/// DToXml 的摘要说明。
/// </summary>
public class XmlToD
{
private string n_XmlFileName="tmp.xml";
public string XmlFileName
{
set
{
n_XmlFileName=value; }
get
{
return n_XmlFileName;
}
} public XmlToD()
{
//
// TODO: 在此处添加构造函数逻辑
//
} public DataSet XmlToSql(string strConn)
{
SqlConnection conn1=new SqlConnection(strConn);
conn1.Open();
SqlDataAdapter adp1=new SqlDataAdapter("select * from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 ",conn1);
DataTable dtTableName=new DataTable("Table_Name");
adp1.Fill(dtTableName);
DataSet ds1=new DataSet();
conn1.Close();
for(int i=0;i<dtTableName.Rows.Count;i++)
{
SqlConnection conn2=new SqlConnection(strConn);
SqlDataAdapter adp2=new SqlDataAdapter("select * from "+dtTableName.Rows[i]["Name"].ToString(),conn2);
DataTable dt2=new DataTable(dtTableName.Rows[i]["name"].ToString());
adp2.Fill(dt2);
ds1.Tables.Add(dt2);
}
ds1.WriteXml(@XmlFileName);
return ds1;
}
public DataSet XmlToAccess(string strConn)
{
OleDbConnection conn=new OleDbConnection(strConn);
DataTable dtTableName=this.GetTables(conn);
DataSet ds1=new DataSet();
for(int i=0;i<dtTableName.Rows.Count;i++)
{
OleDbConnection conn2=new OleDbConnection(strConn);
conn2.Open();
OleDbDataAdapter adp2=new OleDbDataAdapter("select * from "+dtTableName.Rows[i]["Table_name"].ToString(),conn2);
DataTable dt2=new DataTable(dtTableName.Rows[i]["table_name"].ToString());
adp2.Fill(dt2);
ds1.Tables.Add(dt2);
}
ds1.WriteXml(@XmlFileName);
return ds1;
}
private DataTable GetTables(OleDbConnection conn)
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
conn.Close();
return schemaTable;
} public DataSet XmlToDbf(string strConn,string strTable)
{
OleDbConnection conn1=new OleDbConnection(strConn);
conn1.Open();
OleDbDataAdapter adp1=new OleDbDataAdapter("select * from "+strTable,conn1);
DataTable dt1=new DataTable(strTable);
adp1.Fill(dt1);
DataSet ds1=new DataSet();
ds1.Tables.Add(dt1);
ds1.WriteXml(@XmlFileName);
conn1.Close();
return ds1;
}
public DataSet XmlToXls(string strConn)
{
OleDbConnection conn1=new OleDbConnection(strConn);
DataTable dtTableName=this.GetTables(conn1);
DataSet ds1=new DataSet();
for(int i=0;i<dtTableName.Rows.Count;i++)
{
OleDbConnection conn2=new OleDbConnection(strConn);
conn2.Open();
OleDbDataAdapter adp2=new OleDbDataAdapter("select * from ["+dtTableName.Rows[i]["Table_name"].ToString()+"]",conn2);
DataTable dt2=new DataTable(dtTableName.Rows[i]["table_name"].ToString());
adp2.Fill(dt2);
ds1.Tables.Add(dt2);
}
ds1.WriteXml(@XmlFileName);
return ds1;
}
public DataSet XmlToDb(string strConn,string strTable)
{
OleDbConnection conn1=new OleDbConnection(strConn);
conn1.Open();
OleDbDataAdapter adp1=new OleDbDataAdapter("select * from "+strTable,conn1);
DataTable dt1=new DataTable(strTable);
adp1.Fill(dt1);
DataSet ds1=new DataSet();
ds1.Tables.Add(dt1);
ds1.WriteXml(@XmlFileName);
conn1.Close();
return ds1;
} }
}