Oracle SQL Developer 

Toad DBA Suite for Oracle

    

Oracle SQL Developer 

 

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 Name	Number of restrictions	Remarks
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

 

/// 
    /// 对于本文的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    /// 
    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;";        /// 
        ///         /// 
        /// 
             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;        }        /// 
        ///         /// 
        public Form1()        {            InitializeComponent();        }        /// 
        ///         /// 
        ///         ///         private void Form1_Load(object sender, EventArgs e)        {            this.txtConnection.Text = connectionString;            this.comboBox1.DataSource = setDatat();            this.comboBox1.DisplayMember = "name";            this.comboBox1.ValueMember = "id";        }        /// 
        ///         /// 
        ///         ///         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();            }        }        /// 
        ///         /// 
        ///         ///         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