--读取库中的所有表名
select name from sysobjects where xtype='u'
--读取指定表的所有列名
select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')
获取数据库表名和字段 |
sqlserver中各个系统表的作用 syscolumns 每个数据库 列 |
//
用什么方法可以得到一个表中所有的列名。SQl语句。
select 列名=name from syscolumns where id=object_id(N'要查的表名')
use gpStrudy
select name="name" from syscolumns where id=object_id(N'bookTable')
获得字段的属性
//这是从一段代码中拷出来的,使用的是using System.Data.OleDb;
public int GetTableFields(String tableName,out String[] fields,out String[] fieldTypes)
{
try
{
OleDbCommand dc = m_OleDb.CreateCommand();//创建一个执行对象用于执行sql查询
dc.CommandText = "select * from " + tableName;
dc.Transaction = m_OleTrans;
OleDbDataReader dr = dc.ExecuteReader();//执行sql查询
//获取数据库的架构信息
DataTable schemaTable = dr.GetSchemaTable();
int fieldColumnCount = 0;
fields = new String[schemaTable.Rows.Count];
fieldTypes = new String[schemaTable.Rows.Count];
for(int i = 0;i < schemaTable.Columns.Count;i++)
{
if(schemaTable.Columns[i].ColumnName.IndexOf("ColumnName") >= 0)
{
//获取字段名称
for(int k = 0;k < schemaTable.Rows.Count;k++)
fields[k] = schemaTable.Rows[k].ItemArray[i].ToString();
fieldColumnCount++;
if(fieldColumnCount >= 2)
{
dr.Close();
dc.Dispose();
dr = null;
dc = null;
return schemaTable.Rows.Count;
}
}
else if(schemaTable.Columns[i].ColumnName.IndexOf("DataType") >= 0)
{
//获取字段类型
for(int k = 0;k < schemaTable.Rows.Count;k++)
fieldTypes[k] = schemaTable.Rows[k].ItemArray[i].ToString();
fieldColumnCount++;
if(fieldColumnCount >= 2)
{
dr.Close();
dc.Dispose();
dr = null;
dc = null;
return schemaTable.Rows.Count;
}
}
}
dr.Close();
dc.Dispose();
dr = null;
dc = null;
return 0;
}
catch(Exception ee)
{
fields = new String[1];
fieldTypes = new String[1];
m_ErrorString = "Ado_Application:GetTableFields:error:" + ee.Message;
return 0;
}
}Top
//这是获取所有的表
public int GetAllTables(out String[] tables)
{
try
{
//获取数据库的架构信息
DataTable schemaTable = m_OleDb.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
for(int i = 0;i < schemaTable.Columns.Count;i++)
if(schemaTable.Columns[i].ColumnName.IndexOf("TABLE_NAME") >= 0)
{
tables = new String[schemaTable.Rows.Count];
for(int k = 0;k < schemaTable.Rows.Count;k++)
tables[k] = (String)schemaTable.Rows[k].ItemArray[i];
return schemaTable.Rows.Count;
}
tables = new String[1];
return 0;
}
catch(Exception ee)
{
tables = new String[1];
m_ErrorString = "Ado_Application:GetAllTables:error:" + ee.Message;
return 0;
}
}Top
以上代码使用oledb,需要提供连接字符串,比如:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=localhost
该数据库连接字符串连的是本地的sql server2000中的
Northwind数据库