/// <summary>
/// 得到表或视图的结构.
/// </summary>
/// <param name="tableName">表或视图名</param>
/// <returns>表对象</returns>
public static DataTable GetTableOrViewStructure(string tableOrViewName)
{
try
{
// return ConnectOracle.GetDataTable(@"select
// ut.COLUMN_NAME,
// ut.DATA_TYPE,
// ut.DATA_LENGTH,
// uc.comments,
// ut.NULLABLE,
// null as constraint_type
//from user_tab_columns ut
//inner JOIN user_col_comments uc
//on ut.TABLE_NAME = uc.table_name and ut.COLUMN_NAME = uc.column_name
//where ut.Table_Name = '" + tableOrViewName + "' order by ut.column_name");
return(ConnectOracle.GetDataTable(string.Format(@"SELECT
ut.COLUMN_NAME,
ut.DATA_TYPE,
ut.DATA_LENGTH,
uc.comments,
ut.NULLABLE,
A.constraint_type
FROM
user_tab_columns ut
INNER JOIN user_col_comments uc ON ut.TABLE_NAME = uc.table_name
AND ut.COLUMN_NAME = uc.column_name
LEFT JOIN (
SELECT
cu.COLUMN_NAME,
au.constraint_type
FROM
user_cons_columns cu,
user_constraints au
WHERE
cu.constraint_name = au.constraint_name
AND au.table_name = '{0}'
AND au.constraint_type = 'P'
) A ON ut.COLUMN_NAME = A.COLUMN_NAME
WHERE
ut.Table_Name = '{0}'
ORDER BY
ut.column_name", tableOrViewName)));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(null);
}
/// <summary>
/// 得到所有的表名和视图名.
/// </summary>
/// <returns>表对象</returns>
public static DataTable GetAllTableAndViewName()
{
try
{
DataTable dt = ConnectOracle.GetDataTable("select TNAME,TABTYPE from tab where TABTYPE='TABLE' or TABTYPE='VIEW' order by TABTYPE");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["TNAME"].ToString().StartsWith("BIN$"))
{
dt.Rows.Remove(dt.Rows[i]);
i--;
}
}
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(null);
}