SQL Server Schema Collections (ADO.NET)

  • Article

The Microsoft .NET Framework Data Provider for SQL Server supports additional schema collections in addition to the common schema collections. The schema collections vary slightly by the version of SQL Server you are using. To determine the list of supported schema collections, call the GetSchema method with no arguments, or with the schema collection name "MetaDataCollections". This will return a DataTable with a list of the supported schema collections, the number of restrictions that they each support, and the number of identifier parts that they use.

Databases

ColumnName

DataType

Description

database_name

String

Name of the database.

Dbid

Int16

Database ID.

create_date

DateTime

Creation Date of the database.

Foreign Keys

ColumnName

DataType

Description

constraint_catalog

String

Catalog the constraint belongs to.

constraint_schema

String

Schema that contains the constraint.

constraint_name

String

Name.

table_catalog

String

Table Name constraint is part of.

table_schema

String

Schema that that contains the table.

table_name

String

Table Name

constraint_type

String

Type of constraint. Only "FOREIGN KEY" is allowed.

is_deferrable

String

Specifies whether the constraint is deferrable. Returns NO.

initially_deferred

String

Specifies whether the constraint is initially deferrable. Returns NO.

Indexes

ColumnName

DataType

Description

constraint_catalog

String

Catalog that index belongs to.

constraint_schema

String

Schema that contains the index.

constraint_name

String

Name of the index.

table_catalog

String

Table name the index is associated with.

table_schema

String

Schema that contains the table the index is associated with.

table_name

String

Table Name.

Indexes (SQL Server 2008)

Beginning with the .NET Framework version 3.5 SP1 and SQL Server 2008, the following columns have been added to the Indexes schema collection to support new spatial types, filestream and sparse columns. These columns are not supported in earlier versions of the .NET Framework and SQL Server.

ColumnName

DataType

Description

type_desc

String

The type of the index will be one of the following:

  • HEAP

  • CLUSTERED

  • NONCLUSTERED

  • XML

  • SPATIAL

IndexColumns

ColumnName

DataType

Description

constraint_catalog

String

Catalog that index belongs to.

constraint_schema

String

Schema that contains the index.

constraint_name

String

Name of the index.

table_catalog

String

Table name the index is associated with.

table_schema

String

Schema that contains the table the index is associated with.

table_name

String

Table Name.

column_name

String

Column name the index is associated with.

ordinal_position

Int32

Column ordinal position.

KeyType

UInt16

The type of object.

Procedures

This collection is only available in SQL Server 2000 and above.

ColumnName

DataType

Description

specific_catalog

String

Specific name for the catalog.

specific_schema

String

Specific name of the schema.

specific_name

String

Specific name of the catalog.

routine_catalog

String

Catalog the stored procedure belongs to.

routine_schema

String

Schema that contains the stored procedure.

routine_name

String

Name of the stored procedure.

routine_type

String

Returns PROCEDURE for stored procedures and FUNCTION for functions.

created

DateTime

Time the procedure was created.

last_altered

DateTime

The last time the procedure was modified.

Procedure Parameters

ColumnName

DataType

Description

specific_catalog

String

Catalog name of the procedure for which this is a parameter.

specific_schema

String

Schema that contains the procedure for which this parameter is part of.

specific_name

String

Name of the procedure for which this parameter is a part of.

ordinal_position

Int16

Ordinal position of the parameter starting at 1. For the return value of a procedure, this is a 0.

parameter_mode

String

Returns IN if an input parameter, OUT if an output parameter, and INOUT if an input/output parameter.

is_result

String

Returns YES if indicates result of the procedure that is a function. Otherwise, returns NO.

as_locator

String

Returns YES if declared as locator. Otherwise, returns NO.

parameter_name

String

Name of the parameter. NULL if this corresponds to the return value of a function.

data_type

String

System-supplied data type.

character_maximum_length

Int32

Maximum length in characters for binary or character data types. Otherwise, returns NULL.

character_octet_length

Int32

Maximum length, in bytes, for binary or character data types. Otherwise, returns NULL.

collation_catalog

String

Catalog name of the collation of the parameter. If not one of the character types, returns NULL.

collation_schema

String

Always returns NULL.

collation_name

String

Name of the collation of the parameter. If not one of the character types, returns NULL.

character_set_catalog

String

Catalog name of the character set of the parameter. If not one of the character types, returns NULL.

character_set_schema

String

Always returns NULL.

character_set_name

String

Name of the character set of the parameter. If not one of the character types, returns NULL.

numeric_precision

Byte

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

numeric_precision_radix

Int16

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

numeric_scale

Int32

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

datetime_precision

Int16

Precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.

interval_type

String

NULL. Reserved for future use by SQL Server.

interval_precision

Int16

NULL. Reserved for future use by SQL Server.

Tables

ColumnName

DataType

Description

table_catalog

String

Catalog of the table.

table_schema

String

Schema that contains the table.

table_name

String

Table name.

table_type

String

Type of table. Can be VIEW or BASE TABLE.

Columns

ColumnName

DataType

Description

table_catalog

String

Catalog of the table.

table_schema

String

Schema that contains the table.

table_name

String

Table name.

column_name

String

Column name.

ordinal_position

Int16

Column identification number.

column_default

String

Default value of the column

is_nullable

String

Nullability of the column. If this column allows NULL, this column returns YES. Otherwise, No is returned.

data_type

String

System-supplied data type.

character_maximum_length

Int32 – Sql8, Int16 – Sql7

Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.

character_octet_length

Int32 – SQL8, Int16 – Sql7

Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.

numeric_precision

Unsigned Byte

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_precision_radix

Int16

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_scale

Int32

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

datetime_precision

Int16

Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.

character_set_catalog

String

Returns master, indicating the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.

character_set_schema

String

Always returns NULL.

character_set_name

String

Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.

collation_catalog

String

Returns master, indicating the database in which the collation is defined, if the column is character data or text data type. Otherwise, this column is NULL.

Columns (SQL Server 2008)

Beginning with the .NET Framework version 3.5 SP1 and SQL Server 2008, the following columns have been added to the Columns schema collection to support new spatial types, filestream and sparse columns. These columns are not supported in earlier versions of the .NET Framework and SQL Server.

ColumnName

DataType

Description

IS_FILESTREAM

String

YES if the column has FILESTREAM attribute.

NO if the column does not have FILESTREAM attribute.

IS_SPARSE

String

YES if the column is a sparse column.

NO if the column is not a sparse column.

IS_COLUMN_SET

String

YES if the column is a column set column.

NO if the column is not a column set column.

AllColumns (SQL Server 2008)

Beginning with the .NET Framework version 3.5 SP1 and SQL Server 2008, the AllColumns schema collection has been added to support sparse columns. AllColumns is not supported in earlier versions of the .NET Framework and SQL Server.

AllColumns has the same restrictions and resulting DataTable schema as the Columns schema collection. The only difference is that AllColumns includes column set columns that are not included in the Columns schema collection. The following table describes these columns.

ColumnName

DataType

Description

table_catalog

String

Catalog of the table.

table_schema

String

Schema that contains the table.

table_name

String

Table name.

column_name

String

Column name.

ordinal_position

Int16

Column identification number.

column_default

String

Default value of the column

is_nullable

String

Nullability of the column. If this column allows NULL, this column returns YES. Otherwise, NO is returned.

data_type

String

System-supplied data type.

character_maximum_length

Int32 for SQL Server 2000 and later, Int16 for SQL Server 7.0

Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.

character_octet_length

Int32 for SQL Server 2000 and later, Int16 for SQL Server 7.0

Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.

numeric_precision

Unsigned Byte

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_precision_radix

Int16

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_scale

Int32

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

datetime_precision

Int16

Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.

character_set_catalog

String

Returns master, indicating the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.

character_set_schema

String

Always returns NULL.

character_set_name

String

Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.

collation_catalog

String

Returns master, indicating the database in which the collation is defined, if the column is character data or text data type. Otherwise, this column is NULL.

IS_FILESTREAM

String

YES if the column has FILESTREAM attribute.

NO if the column does not have FILESTREAM attribute.

IS_SPARSE

String

YES if the column is a sparse column.

NO if the column is not a sparse column.

IS_COLUMN_SET

String

YES if the column is a column set column.

NO if the column is not a column set column.

ColumnSetColumns (SQL Server 2008)

Beginning with the .NET Framework version 3.5 SP1 and SQL Server 2008, the ColumnSetColumns schema collection has been added to support sparse columns. ColumnSetColumns is not supported in earlier versions of the .NET Framework and SQL Server. The ColumnSetColumns schema collection returns the schema for all of the columns in a column set. The following table describes these columns.

ColumnName

DataType

Description

table_catalog

String

Catalog of the table.

table_schema

String

Schema that contains the table.

table_name

String

Table name.

column_name

String

Column name.

ordinal_position

Int16

Column identification number.

column_default

String

Default value of the column

is_nullable

String

Nullability of the column. If this column allows NULL, this column returns YES. Otherwise, NO is returned.

data_type

String

System-supplied data type.

character_maximum_length

Int32 for SQL Server 2000 and later, Int16 for SQL Server 7.0

Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.

character_octet_length

Int32 for SQL Server 2000 and later, Int16 for SQL Server 7.0

Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.

numeric_precision

Unsigned Byte

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_precision_radix

Int16

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_scale

Int32

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

datetime_precision

Int16

Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.

character_set_catalog

String

Returns master, indicating the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.

character_set_schema

String

Always returns NULL.

character_set_name

String

Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.

collation_catalog

String

Returns master, indicating the database in which the collation is defined, if the column is character data or text data type. Otherwise, this column is NULL.

IS_FILESTREAM

String

YES if the column has FILESTREAM attribute.

NO if the column does not have FILESTREAM attribute.

IS_SPARSE

String

YES if the column is a sparse column.

NO if the column is not a sparse column.

IS_COLUMN_SET

String

YES if the column is a column set column.

NO if the column is not a column set column.

Users

ColumnName

DataType

Description

uid

Int16

User ID, unique in this database. 1 is the database owner.

name

String

Username or group name, unique in this database.

createdate

DateTime

Date the account was added.

updatedate

DateTime

Date the account was last changed.

Views

This collection is only available in SQL Server 2000 and above.

ColumnName

DataType

Description

table_catalog

String

Catalog of the view.

table_schema

String

Schema that contains the view.

table_name

String

View name.

check_option

String

Type of WITH CHECK OPTION. Is CASCADE if the original view was created using the WITH CHECK OPTION. Otherwise, NONE is returned.

is_updatable

String

Specifies whether the view is updatable. Always returns NO.

ViewColumns

ColumnName

DataType

Description

view_catalog

String

Catalog of the view.

view_schema

String

Schema that contains the view.

view_name

String

View name.

table_catalog

String

Catalog of the table that is associated with this view.

table_schema

String

Schema that contains the table that is associated with this view.

table_name

String

Name of the table that is associated with the view. Base Table.

column_name

String

Column name.

UserDefinedTypes

This collection is only available in SQL Server 2005 and above.

ColumnName

DataType

Description

assembly_name

String

The name of the file for the assembly.

UDT_name

String

The class name for the assembly.

version_major

Object

Major Version Number.

version_minor

Object

Minor Version Number.

version_build

Object

Build Number.

version_revision

Object

Revision Number.

Culture_info

Object

The culture information associated with this UDT.

Public_key

Object

The public key used by this Assembly.

Is_fixed_length

Boolean

Specifies whether length of type is always same as max_length.

max_length

Int16

Maximum length of type in bytes.

permission_set_desc

String

The friendly name for the permission-set/security-level for the assembly.

create_date

DateTime

The date the assembly was created/registered.

See Also

Other Resources

Retrieving Database Schema Information (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center