Oracle Metadata

http://www.devart.com/dotconnect/oracle/articles/metadata.html
http://dcx.sybase.com/1101/en/dbprogramming_en11/ianywhere-data-sqlanywhere-saconnection-getschem6330755502-0.html

Oracle SQL Developer 

Toad DBA Suite for Oracle

http://software.dell.com/products/toad-dba-suite-for-oracle/    

Oracle SQL Developer 

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

 

In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. Quantity of elements in the array must be less or equal to the value that is returned by GetSchema() method in the second cell of the row that corresponds to the collection name. (Or from the table below, which is much more handy.) If the second argument is null (Nothing), the function behaves like the previous overload (that takes a single parameter).
GetSchema Method Reference

 

 

Collection NameNumber of restrictionsRemarks
MetaDataCollections 0
Returns this list. Same as using GetSchema() method without parameters.
ReservedWords 0
Lists all reserved words used in the server.
Users 1
Lists all users on the server.
When restricted by username, returns information about specific user.
Tables 2
GetSchema("Tables") Returns the list of all tables on the server that you have access to.
The first restriction for this collection is name of a schema. If specified, the method returns all tables within the schema.
The second restriction is table name. Note that masks are not allowed in dotConnect for Oracle.
Views 2
GetSchema("Views") Returns the list of all views on the server that you have access to.
The first restriction for this collection is name of a schema. If specified, the method returns all views within the schema.
The second restriction is the name of the view.
Columns 3
Returns the list of columns, their type and some extra information.
GetSchema("Columns") Returns the list of all columns in all schemas of the table.
Restricted by schema name, the method returns all columns in the specified schema.
The second restriction is name of a table that GetSchema method should search in.
At last, you can specify column name.
Indexes 4
Returns the list of indexes and their details.
The first restriction is name of a schema the indexes belongs to.
The second restriction is name of the index.
The third restriction is name of a table that uses the index.
The last restriction is name of schema the table belongs to.
IndexColumns 5
Returns information about columns included in indexes. The following restrictions may be specified:
Name of the schema for indexes;
Index name;
Name of the schema for tables;
Table name;
Column name.
Functions 2
Returns the list of functions on the server. The following restrictions may be specified:
Schema name;
Function name.
Procedures 3
Returns the list of procedures on the server. The following restrictions may be specified:
Schema name;
Package name;
Procedure name.
Arguments 4
Returns the list of procedure and function arguments. The following restrictions may be specified:
Schema name;
Package name;
Procedure name;
Argument name.
Synonyms 2
Returns the list of synonyms on the server. The following restrictions may be specified:
Schema name;
Synonym name.
Sequences 2
Returns the list of sequences on the server. The following restrictions may be specified:
Schema name;
Sequence name.
Packages 2
Returns the list of packages on the server. The following restrictions may be specified:
Schema name;
Package name.
PackageBodies 2
Returns the list of package bodies on the server that you have access to. The following restrictions may be specified:
Schema name;
Package name.
ForeignKeys 3
Returns the list of foreign keys on the server. The following restrictions may be specified:
Schema name;
Key name;
Table name.
ForeignKeyColumns 3
Returns the list of columns of foreign keys on the server. The following restrictions may be specified:
Schema name;
Key name;
Table name.
Triggers 2
Returns the list of triggers on the server that you have access to. The following restrictions may be specified:
Schema name;
Trigger name.
Clusters 2
Returns the list of clusters on the server that you have access to. The following restrictions may be specified:
Schema name;
Cluster name.
TypeName ProviderDbType ColumnSize CreateFormat CreateParameters DataType
BFILE 1 4294967296 BFILE   System.Byte[]
BLOB 2 4294967296 BLOB   System.Byte[]
CHAR 3 2000 CHAR({0}) size System.String
CLOB 4 4294967296 CLOB   System.String
DATE 6 19 DATE   System.DateTime
FLOAT 29 38 FLOAT   System.Decimal
INTERVAL DAY TO SECOND 7 0 INTERVAL DAY({0}) TO SECOND({1}) dayprecision,secondsprecision System.TimeSpan
INTERVAL YEAR TO MONTH 8 0 INTERVAL YEAR({0}) TO MONTH yearprecision System.Int32
LONG 10 2147483647 LONG   System.String
LONG RAW 9 2147483647 LONG RAW   System.Byte[]
NCHAR 11 2000 NCHAR({0}) size System.String
NCLOB 12 4294967296 NCLOB   System.String
NUMBER 13 38 NUMBER ({0},{1}) precision,scale System.Decimal
NVARCHAR2 14 4000 NVARCHAR2({0}) size System.String
RAW 15 2000 RAW({0}) size System.Byte[]
ROWID 16 3950 ROWID   System.String
TIMESTAMP 18 27 TIMESTAMP({0}) precision of fractional seconds System.DateTime
TIMESTAMP WITH LOCAL TIME ZONE 19 27 TIMESTAMP({0} WITH LOCAL TIME ZONE) precision of fractional seconds System.DateTime
TIMESTAMP WITH TIME ZONE 20 34 TIMESTAMP({0} WITH TIME ZONE) precision of fractional seconds System.DateTime
VARCHAR2 22 4000 VARCHAR2({0}) size System.String

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
/// <summary>
/// 对于本文的Oracle安装来说，data source对应着Oracle_Client\oracle\ora92\network\admin\tnsnames.ora配置文件中的网络服务名
/// Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = geovin)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = geovindu)));user id=sysdba;password=0214;Persist Security Info=True;";
/// 涂聚文 20150516
/// </summary>
public partial class Form1 : Form
{
    public string connectionString = @"Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = geovistu-xwvuyh)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle9i)));user id=geovin;password=0214;Persist Security Info=True;";
    /// <summary>
    ///
    /// </summary>
    /// <returns></returns>
    DataTable setDatat()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("id", typeof(int));
        dt.Columns.Add("name", typeof(string));
        dt.Rows.Add(1, "Procedures");
        dt.Rows.Add(2, "DataTypes");
        dt.Rows.Add(3, "Foreign Keys");
        dt.Rows.Add(4, "Databases");
        dt.Rows.Add(5, "dbo");
        dt.Rows.Add(6, "Arguments");
        dt.Rows.Add(7, "Collection Name");
        dt.Rows.Add(8, "DatasourceInformation");
        dt.Rows.Add(9, "MetaDataCollections");
        dt.Rows.Add(10, "ForeignKeyColumns");
        dt.Rows.Add(11, "Functions");
        dt.Rows.Add(12, "IndexColumns");
        dt.Rows.Add(13, "Indexes");
        dt.Rows.Add(14, "PrimaryKeys");
        dt.Rows.Add(15, "ReservedWords");
        dt.Rows.Add(16, "Restrictions");
        dt.Rows.Add(17, "Triggers");
        dt.Rows.Add(18, "UDFs");
        dt.Rows.Add(19, "UniqueKeys");
        dt.Rows.Add(20, "UserPrivileges");
        dt.Rows.Add(21, "Users");
        dt.Rows.Add(22, "ViewColumns");
 
        //DataTable dt = connection.GetSchema("Tables", strRestricted);
        dt.Rows.Add(23, "Tables");
        dt.Rows.Add(24, "Columns");//表的列的详细，有主键TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,PRIVILEGES,COLUMN_COMMENT
        dt.Rows.Add(25, "Views");
        dt.Rows.Add(26, "Indexes");//表的列
        dt.Rows.Add(27, "IndexColumns");//主键
 
        return dt;
    }
    /// <summary>
    ///
    /// </summary>
    public Form1()
    {
        InitializeComponent();
    }
 
    /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void Form1_Load(object sender, EventArgs e)
    {
        this.txtConnection.Text = connectionString;
        this.comboBox1.DataSource = setDatat();
        this.comboBox1.DisplayMember = "name";
        this.comboBox1.ValueMember = "id";
    }
 
    /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                //MessageBox.Show(connection.State.ToString());
                DataTable dt = connection.GetSchema(this.comboBox1.Text.Trim());
                this.dataGridView1.DataSource = dt;
                this.textBox1.Text = GetColumnNames(dt);
 
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
            ex.Message.ToString();
        }
    }
    /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button2_Click(object sender, EventArgs e)
    {
        string connString = "Provider=OraOLEDB.Oracle.1;User ID=geovin;Password=0214;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = geovistu-xwvuyh)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle9i)))";
        OleDbConnection conn = new OleDbConnection(connString);
        try
        {
            conn.Open();
            //MessageBox.Show(conn.State.ToString());
            DataTable dt = conn.GetSchema(this.comboBox1.Text.Trim());
            this.dataGridView1.DataSource = dt;
            this.textBox1.Text = GetColumnNames(dt);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
        finally
        {
            conn.Close();
        }
    }

　　用 

using Oracle.DataAccess.Client;

MetaDataCollections
DataSourceInformation
DataTypes
Restrictions
ReservedWords
Users
Tables
Columns
Views
Synonyms
Sequences
Functions
Procedures
Packages
PackageBodies
IndexColumns
Indexes
ProcedureParameters
Arguments
UniqueKeys
PrimaryKeys
ForeignKeys
ForeignKeyColumns
JavaClasses
XMLSchemas

 

posted @   ®Geovin Du Dream Park™  阅读(469)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示