SqlConnection.GetSchema SqlConnection.GetSchema SqlConnection.GetSchema SqlConnection.GetSchema Method

定義

この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection.

オーバーロード

GetSchema() GetSchema() GetSchema() GetSchema()

この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection. スキームの詳細については、「SQL Server スキーマ コレクション」を参照してください。For more information about scheme, see SQL Server Schema Collections.

GetSchema(String) GetSchema(String) GetSchema(String) GetSchema(String)

スキーマ名として指定した文字列を使用して、この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection using the specified string for the schema name.

GetSchema(String, String[]) GetSchema(String, String[]) GetSchema(String, String[]) GetSchema(String, String[])

スキーマ名に指定した文字列と制限値に指定した文字列配列を使用して、この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection using the specified string for the schema name and the specified string array for the restriction values.

注釈

複数のバージョンがあるストアドプロシージャのスキーマ情報を取得しようとすると、最新のもののみのスキーマが返されます。If you attempt to retrieve schema information for more than one versioned stored procedure, the schema for the latest one only is returned.

GetSchema() GetSchema() GetSchema() GetSchema()

この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection. スキームの詳細については、「SQL Server スキーマ コレクション」を参照してください。For more information about scheme, see SQL Server Schema Collections.

public:
 override System::Data::DataTable ^ GetSchema();
public override System.Data.DataTable GetSchema ();
override this.GetSchema : unit -> System.Data.DataTable
Public Overrides Function GetSchema () As DataTable

戻り値

スキーマ情報を格納する DataTableA DataTable that contains schema information.

こちらもご覧ください

GetSchema(String) GetSchema(String) GetSchema(String) GetSchema(String)

スキーマ名として指定した文字列を使用して、この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection using the specified string for the schema name.

public:
 override System::Data::DataTable ^ GetSchema(System::String ^ collectionName);
public override System.Data.DataTable GetSchema (string collectionName);
override this.GetSchema : string -> System.Data.DataTable
Public Overrides Function GetSchema (collectionName As String) As DataTable

パラメーター

collectionName
String String String String

返すスキーマの名前を指定します。Specifies the name of the schema to return.

戻り値

スキーマ情報を格納する DataTableA DataTable that contains schema information.

例外

collectionName は null として指定されます。collectionName is specified as null.

注釈

データベース、テーブル、または列のスキーマ情報が必要になる場合があります。You may need the schema information of the database, tables or columns. このサンプルは次のとおりです。This sample:

  • は、GetSchema を使用してスキーマ情報を取得します。Uses GetSchema to get schema information.

  • 指定された情報を取得するには、スキーマ制限を使用します。Use schema restrictions to get the specified information.

  • データベース、テーブル、およびいくつかの列のスキーマ情報を取得します。Gets schema information of the database, tables, and some columns.

サンプルを実行する前に、次Transact-SQLTransact-SQLのコードを使用してサンプルデータベースを作成する必要があります。Before you run the sample, you need to create the sample database, using the following Transact-SQLTransact-SQL:

USE [master]  
GO  

CREATE DATABASE [MySchool]   

GO  

USE [MySchool]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,  
[Year] [smallint] NOT NULL,  
[Title] [nvarchar](100) NOT NULL,  
[Credits] [int] NOT NULL,  
[DepartmentID] [int] NOT NULL,  
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED   
(  
[CourseID] ASC,  
[Year] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,  
[Name] [nvarchar](50) NOT NULL,  
[Budget] [money] NOT NULL,  
[StartDate] [datetime] NOT NULL,  
[Administrator] [int] NULL,  
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED   
(  
[DepartmentID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)  

SET IDENTITY_INSERT [dbo].[Department] ON   

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)  
SET IDENTITY_INSERT [dbo].[Department] OFF  

ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])  
REFERENCES [dbo].[Department] ([DepartmentID])  
GO  
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]  
GO  

データベースからスキーマ情報を取得する方法についてはC# 、Visual Studio プロジェクトで、このコードサンプルのバージョンと Visual Basic について説明します。How to Get Schema Information from Database has C# and Visual Basic versions of this code sample in a Visual Studio project.

using System;  
using System.Data;  
using System.Data.SqlClient;  

class Program {  
   static void Main(string[] args) {  

      using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=MySchool;Integrated Security=True;Asynchronous Processing=true;")) {  
         conn.Open();  

         // Get the Meta Data for Supported Schema Collections  
         DataTable metaDataTable = conn.GetSchema("MetaDataCollections");  

         Console.WriteLine("Meta Data for Supported Schema Collections:");  
         ShowDataTable(metaDataTable, 25);  
         Console.WriteLine();  

         // Get the schema information of Databases in your instance  
         DataTable databasesSchemaTable = conn.GetSchema("Databases");  

         Console.WriteLine("Schema Information of Databases:");  
         ShowDataTable(databasesSchemaTable, 25);  
         Console.WriteLine();  

         // First, get schema information of all the tables in current database;  
         DataTable allTablesSchemaTable = conn.GetSchema("Tables");  

         Console.WriteLine("Schema Information of All Tables:");  
         ShowDataTable(allTablesSchemaTable, 20);  
         Console.WriteLine();  

         // You can specify the Catalog, Schema, Table Name, Table Type to get   
         // the specified table(s).  
         // You can use four restrictions for Table, so you should create a 4 members array.  
         String[] tableRestrictions = new String[4];  

         // For the array, 0-member represents Catalog; 1-member represents Schema;   
         // 2-member represents Table Name; 3-member represents Table Type.   
         // Now we specify the Table Name of the table what we want to get schema information.  
         tableRestrictions[2] = "Course";  

         DataTable courseTableSchemaTable = conn.GetSchema("Tables", tableRestrictions);  

         Console.WriteLine("Schema Information of Course Tables:");  
         ShowDataTable(courseTableSchemaTable, 20);  
         Console.WriteLine();  

         // First, get schema information of all the columns in current database.  
         DataTable allColumnsSchemaTable = conn.GetSchema("Columns");  

         Console.WriteLine("Schema Information of All Columns:");  
         ShowColumns(allColumnsSchemaTable);  
         Console.WriteLine();  

         // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).  
         // You can use four restrictions for Column, so you should create a 4 members array.  
         String[] columnRestrictions = new String[4];  

         // For the array, 0-member represents Catalog; 1-member represents Schema;   
         // 2-member represents Table Name; 3-member represents Column Name.   
         // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.  
         columnRestrictions[2] = "Course";  
         columnRestrictions[3] = "DepartmentID";  

         DataTable departmentIDSchemaTable = conn.GetSchema("Columns", columnRestrictions);  

         Console.WriteLine("Schema Information of DepartmentID Column in Course Table:");  
         ShowColumns(departmentIDSchemaTable);  
         Console.WriteLine();  

         // First, get schema information of all the IndexColumns in current database  
         DataTable allIndexColumnsSchemaTable = conn.GetSchema("IndexColumns");  

         Console.WriteLine("Schema Information of All IndexColumns:");  
         ShowIndexColumns(allIndexColumnsSchemaTable);  
         Console.WriteLine();  

         // You can specify the Catalog, Schema, Table Name, Constraint Name, Column Name to   
         // get the specified column(s).  
         // You can use five restrictions for Column, so you should create a 5 members array.  
         String[] indexColumnsRestrictions = new String[5];  

         // For the array, 0-member represents Catalog; 1-member represents Schema;   
         // 2-member represents Table Name; 3-member represents Constraint Name;4-member represents Column Name.   
         // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.  
         indexColumnsRestrictions[2] = "Course";  
         indexColumnsRestrictions[4] = "CourseID";  

         DataTable courseIdIndexSchemaTable = conn.GetSchema("IndexColumns", indexColumnsRestrictions);  

         Console.WriteLine("Index Schema Information of CourseID Column in Course Table:");  
         ShowIndexColumns(courseIdIndexSchemaTable);  
         Console.WriteLine();  
      }  

      Console.WriteLine("Please press any key to exit...");  
      Console.ReadKey();  
   }  

   private static void ShowDataTable(DataTable table, Int32 length) {  
      foreach (DataColumn col in table.Columns) {  
         Console.Write("{0,-" + length + "}", col.ColumnName);  
      }  
      Console.WriteLine();  

      foreach (DataRow row in table.Rows) {  
         foreach (DataColumn col in table.Columns) {  
            if (col.DataType.Equals(typeof(DateTime)))  
               Console.Write("{0,-" + length + ":d}", row[col]);  
            else if (col.DataType.Equals(typeof(Decimal)))  
               Console.Write("{0,-" + length + ":C}", row[col]);  
            else  
               Console.Write("{0,-" + length + "}", row[col]);  
         }  
         Console.WriteLine();  
      }  
   }  

   private static void ShowDataTable(DataTable table) {  
      ShowDataTable(table, 14);  
   }  

   private static void ShowColumns(DataTable columnsTable) {  
      var selectedRows = from info in columnsTable.AsEnumerable()  
                         select new {  
                            TableCatalog = info["TABLE_CATALOG"],  
                            TableSchema = info["TABLE_SCHEMA"],  
                            TableName = info["TABLE_NAME"],  
                            ColumnName = info["COLUMN_NAME"],  
                            DataType = info["DATA_TYPE"]  
                         };  

      Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}", "TableCatalog", "TABLE_SCHEMA",  
          "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE");  
      foreach (var row in selectedRows) {  
         Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}", row.TableCatalog,  
             row.TableSchema, row.TableName, row.ColumnName, row.DataType);  
      }  
   }  

   private static void ShowIndexColumns(DataTable indexColumnsTable) {  
      var selectedRows = from info in indexColumnsTable.AsEnumerable()  
                         select new {  
                            TableSchema = info["table_schema"],  
                            TableName = info["table_name"],  
                            ColumnName = info["column_name"],  
                            ConstraintSchema = info["constraint_schema"],  
                            ConstraintName = info["constraint_name"],  
                            KeyType = info["KeyType"]  
                         };  

      Console.WriteLine("{0,-14}{1,-11}{2,-14}{3,-18}{4,-16}{5,-8}", "table_schema", "table_name", "column_name", "constraint_schema", "constraint_name", "KeyType");  
      foreach (var row in selectedRows) {  
         Console.WriteLine("{0,-14}{1,-11}{2,-14}{3,-18}{4,-16}{5,-8}", row.TableSchema,  
             row.TableName, row.ColumnName, row.ConstraintSchema, row.ConstraintName, row.KeyType);  
      }  
   }  
}  
こちらもご覧ください

GetSchema(String, String[]) GetSchema(String, String[]) GetSchema(String, String[]) GetSchema(String, String[])

スキーマ名に指定した文字列と制限値に指定した文字列配列を使用して、この SqlConnection のデータ ソースのスキーマ情報を返します。Returns schema information for the data source of this SqlConnection using the specified string for the schema name and the specified string array for the restriction values.

public:
 override System::Data::DataTable ^ GetSchema(System::String ^ collectionName, cli::array <System::String ^> ^ restrictionValues);
public override System.Data.DataTable GetSchema (string collectionName, string[] restrictionValues);
override this.GetSchema : string * string[] -> System.Data.DataTable
Public Overrides Function GetSchema (collectionName As String, restrictionValues As String()) As DataTable

パラメーター

collectionName
String String String String

返すスキーマの名前を指定します。Specifies the name of the schema to return.

restrictionValues
String[]

要求したスキーマの制限値のセット。A set of restriction values for the requested schema.

戻り値

スキーマ情報を格納する DataTableA DataTable that contains schema information.

例外

collectionName は null として指定されます。collectionName is specified as null.

注釈

パラメーター restrictionValuesでは、特定のコレクションの制限コレクションによって指定される値のnの深さを指定できます。The restrictionValues parameter can supply n depth of values, which are specified by the restrictions collection for a specific collection. 特定の制限に値を設定し、他の制限値を設定しないようにするには、上記の制限値nullをに設定してから、に値を指定する制限の適切な値を入力する必要があります。In order to set values on a given restriction, and not set the values of other restrictions, you need to set the preceding restrictions to null and then put the appropriate value in for the restriction that you would like to specify a value for.

この例として、"Tables" コレクションがあります。An example of this is the "Tables" collection. "Tables" コレクションに3つの制限 (データベース、所有者、およびテーブル名) があり、所有者 "Carl" に関連付けられているテーブルのみを取得する場合は、次の値を渡す必要があります: null、"Carl"。If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". 制限値が渡されない場合は、その制限に既定値が使用されます。If a restriction value is not passed in, the default values are used for that restriction. これはnull、を渡す場合と同じマッピングです。これは、パラメーター値として空の文字列を渡すこととは異なります。This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. その場合、空の文字列 ("") は、指定されたパラメーターの値と見なされます。In that case, the empty string ("") is considered to be the value for the specified parameter.

GetSchemaを示すコードサンプルについてGetSchemaは、「」を参照してください。For a code sample demonstrating GetSchema, see GetSchema.

こちらもご覧ください

適用対象