Schema Restrictions

 

The second optional parameter of the GetSchema method is the restrictions that are used to limit the amount of schema information returned, and it is passed to the GetSchema method as an array of strings. The position in the array determines the values that you can pass, and this is equivalent to the restriction number.

For example, the following table describes the restrictions supported by the "Tables" schema collection using the .NET Framework Data Provider for SQL Server. Additional restrictions for SQL Server schema collections are listed at the end of this topic.

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@NameTABLE_NAME3
TableType@TableTypeTABLE_TYPE4

To use one of the restrictions of the "Tables" schema collection, simply create an array of strings with four elements, then place a value in the element that matches the restriction number. For example, to restrict the tables returned by the GetSchema method to only those tables in the "Sales" schema, set the second element of the array to "Sales" before passing it to the GetSchema method.

System_CAPS_ICON_note.jpg Note

The restrictions collections for SqlClient and OracleClient have an additional ParameterName column. The restriction default column is still there for backwards compatibility, but is currently ignored. Parameterized queries rather than string replacement should be used to minimize the risk of an SQL injection attack when specifying restriction values.

System_CAPS_ICON_note.jpg Note

The number of elements in the array must be less than or equal to the number of restrictions supported for the specified schema collection else an ArgumentException will be thrown. There can be fewer than the maximum number of restrictions. The missing restrictions are assumed to be null (unrestricted).

You can query a .NET Framework managed provider to determine the list of supported restrictions by calling the GetSchema method with the name of the restrictions schema collection, which is "Restrictions". This will return a DataTable with a list of the collection names, the restriction names, the default restriction values, and the restriction numbers.

Example

The following examples demonstrate how to use the GetSchema method of the .NET Framework Data Provider for the SQL Server SqlConnection class to retrieve schema information about all of the tables contained in the AdventureWorks sample database, and to restrict the information returned to only those tables in the "Sales" schema:

[Visual Basic]

Imports System.Data.SqlClient  
  
Module Module1  
Sub Main()  
  Dim connectionString As String = _  
    "Data Source=(local);Database=AdventureWorks;" & _  
       "Integrated Security=true;";  
  
  Dim restrictions(3) As String  
  Using connection As New SqlConnection(connectionString)  
    connection.Open()  
  
    'Specify the restrictions.  
    restrictions(1) = "Sales"  
    Dim table As DataTable = connection.GetSchema("Tables", _  
       restrictions)  
  
    ' Display the contents of the table.  
      For Each row As DataRow In table.Rows  
         For Each col As DataColumn In table.Columns  
            Console.WriteLine("{0} = {1}", col.ColumnName, row(col))  
         Next  
         Console.WriteLine("============================")  
      Next  
    Console.WriteLine("Press any key to continue.")  
    Console.ReadKey()  
  End Using  
End Sub  
End Module  

[C#]

using System;  
using System.Data;  
using System.Data.SqlClient;  
  
class Program  
{  
  static void Main()  
  {  
    string connectionString =   
       "Data Source=(local);Database=AdventureWorks;" +  
       "Integrated Security=true;";  
    using (SqlConnection connection =  
       new SqlConnection(connectionString))  
    {  
        connection.Open();  
  
        // Specify the restrictions.  
        string[] restrictions = new string[4];  
        restrictions[1] = "Sales";  
        System.Data.DataTable table = connection.GetSchema(  
          "Tables", restrictions);  
  
        // Display the contents of the table.  
        foreach (System.Data.DataRow row in table.Rows)  
        {  
            foreach (System.Data.DataColumn col in table.Columns)  
            {  
                Console.WriteLine("{0} = {1}",   
                  col.ColumnName, row[col]);  
            }  
            Console.WriteLine("============================");  
        }  
        Console.WriteLine("Press any key to continue.");  
        Console.ReadKey();  
    }  
  }  
  
  private static string GetConnectionString()  
  {  
     // To avoid storing the connection string in your code,  
     // you can retrieve it from a configuration file.  
     return "Data Source=(local);Database=AdventureWorks;" +  
        "Integrated Security=true;";  
  }  
  
  private static void DisplayData(System.Data.DataTable table)  
  {  
     foreach (System.Data.DataRow row in table.Rows)  
     {  
        foreach (System.Data.DataColumn col in table.Columns)  
        {  
           Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);  
        }  
     Console.WriteLine("============================");  
     }  
  }  
}  

The following tables list the restrictions for SQL Server schema collections.

Users

Restriction NameParameter NameRestriction DefaultRestriction Number
User_Name@Namename1

Databases

Restriction NameParameter NameRestriction DefaultRestriction Number
Name@NameName1

Tables

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@NameTABLE_NAME3
TableType@TableTypeTABLE_TYPE4

Columns

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@TableTABLE_NAME3
Column@ColumnCOLUMN_NAME4

StructuredTypeMembers

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@TableTABLE_NAME3
Column@ColumnCOLUMN_NAME4

Views

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@TableTABLE_NAME3

ViewColumns

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogVIEW_CATALOG1
Owner@OwnerVIEW_SCHEMA2
Table@TableVIEW_NAME3
Column@ColumnCOLUMN_NAME4

ProcedureParameters

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogSPECIFIC_CATALOG1
Owner@OwnerSPECIFIC_SCHEMA2
Name@NameSPECIFIC_NAME3
Parameter@ParameterPARAMETER_NAME4

Procedures

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogSPECIFIC_CATALOG1
Owner@OwnerSPECIFIC_SCHEMA2
Name@NameSPECIFIC_NAME3
Type@TypeROUTINE_TYPE4

IndexColumns

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@Catalogdb_name()1
Owner@Owneruser_name()2
Table@Tableo.name3
ConstraintName@ConstraintNamex.name4
Column@Columnc.name5

Indexes

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@Catalogdb_name()1
Owner@Owneruser_name()2
Table@Tableo.name3

UserDefinedTypes

Restriction NameParameter NameRestriction DefaultRestriction Number
assembly_name@AssemblyNameassemblies.name1
udt_name@UDTNametypes.assembly_class2

ForeignKeys

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogCONSTRAINT_CATALOG1
Owner@OwnerCONSTRAINT_SCHEMA2
Table@TableTABLE_NAME3
Name@NameCONSTRAINT_NAME4

The following tables list the restrictions for SQL Server 2008 schema collections. These restrictions are valid beginning with version 3.5 SP1 of the .NET Framework and SQL Server 2008. They are not supported in earlier versions of the .NET Framework and SQL Server.

ColumnSetColumns

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@TableTABLE_NAME3

AllColumns

Restriction NameParameter NameRestriction DefaultRestriction Number
Catalog@CatalogTABLE_CATALOG1
Owner@OwnerTABLE_SCHEMA2
Table@TableTABLE_NAME3
Column@ColumnCOLUMN_NAME4

ADO.NET Managed Providers and DataSet Developer Center

Show: