SQL Server Schema Collections

 

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.

ColumnNameDataTypeDescription
database_nameStringName of the database.
DbidInt16Database ID.
create_dateDateTimeCreation Date of the database.
ColumnNameDataTypeDescription
constraint_catalogStringCatalog the constraint belongs to.
constraint_schemaStringSchema that contains the constraint.
constraint_nameStringName.
table_catalogStringTable Name constraint is part of.
table_schemaStringSchema that that contains the table.
table_nameStringTable Name
constraint_typeStringType of constraint. Only "FOREIGN KEY" is allowed.
is_deferrableStringSpecifies whether the constraint is deferrable. Returns NO.
initially_deferredStringSpecifies whether the constraint is initially deferrable. Returns NO.
ColumnNameDataTypeDescription
constraint_catalogStringCatalog that index belongs to.
constraint_schemaStringSchema that contains the index.
constraint_nameStringName of the index.
table_catalogStringTable name the index is associated with.
table_schemaStringSchema that contains the table the index is associated with.
table_nameStringTable 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.

ColumnNameDataTypeDescription
type_descStringThe type of the index will be one of the following:

- HEAP
- CLUSTERED
- NONCLUSTERED
- XML
- SPATIAL
ColumnNameDataTypeDescription
constraint_catalogStringCatalog that index belongs to.
constraint_schemaStringSchema that contains the index.
constraint_nameStringName of the index.
table_catalogStringTable name the index is associated with.
table_schemaStringSchema that contains the table the index is associated with.
table_nameStringTable Name.
column_nameStringColumn name the index is associated with.
ordinal_positionInt32Column ordinal position.
KeyTypeUInt16The type of object.
ColumnNameDataTypeDescription
specific_catalogStringSpecific name for the catalog.
specific_schemaStringSpecific name of the schema.
specific_nameStringSpecific name of the catalog.
routine_catalogStringCatalog the stored procedure belongs to.
routine_schemaStringSchema that contains the stored procedure.
routine_nameStringName of the stored procedure.
routine_typeStringReturns PROCEDURE for stored procedures and FUNCTION for functions.
createdDateTimeTime the procedure was created.
last_alteredDateTimeThe last time the procedure was modified.
ColumnNameDataTypeDescription
specific_catalogStringCatalog name of the procedure for which this is a parameter.
specific_schemaStringSchema that contains the procedure for which this parameter is part of.
specific_nameStringName of the procedure for which this parameter is a part of.
ordinal_positionInt16Ordinal position of the parameter starting at 1. For the return value of a procedure, this is a 0.
parameter_modeStringReturns IN if an input parameter, OUT if an output parameter, and INOUT if an input/output parameter.
is_resultStringReturns YES if indicates result of the procedure that is a function. Otherwise, returns NO.
as_locatorStringReturns YES if declared as locator. Otherwise, returns NO.
parameter_nameStringName of the parameter. NULL if this corresponds to the return value of a function.
data_typeStringSystem-supplied data type.
character_maximum_lengthInt32Maximum length in characters for binary or character data types. Otherwise, returns NULL.
character_octet_lengthInt32Maximum length, in bytes, for binary or character data types. Otherwise, returns NULL.
collation_catalogStringCatalog name of the collation of the parameter. If not one of the character types, returns NULL.
collation_schemaStringAlways returns NULL.
collation_nameStringName of the collation of the parameter. If not one of the character types, returns NULL.
character_set_catalogStringCatalog name of the character set of the parameter. If not one of the character types, returns NULL.
character_set_schemaStringAlways returns NULL.
character_set_nameStringName of the character set of the parameter. If not one of the character types, returns NULL.
numeric_precisionBytePrecision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.
numeric_precision_radixInt16Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.
numeric_scaleInt32Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.
datetime_precisionInt16Precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.
interval_typeStringNULL. Reserved for future use by SQL Server.
interval_precisionInt16NULL. Reserved for future use by SQL Server.
ColumnNameDataTypeDescription
table_catalogStringCatalog of the table.
table_schemaStringSchema that contains the table.
table_nameStringTable name.
table_typeStringType of table. Can be VIEW or BASE TABLE.
ColumnNameDataTypeDescription
table_catalogStringCatalog of the table.
table_schemaStringSchema that contains the table.
table_nameStringTable name.
column_nameStringColumn name.
ordinal_positionInt16Column identification number.
column_defaultStringDefault value of the column
is_nullableStringNullability of the column. If this column allows NULL, this column returns YES. Otherwise, No is returned.
data_typeStringSystem-supplied data type.
character_maximum_lengthInt32 – Sql8, Int16 – Sql7Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.
character_octet_lengthInt32 – SQL8, Int16 – Sql7Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.
numeric_precisionUnsigned BytePrecision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
numeric_precision_radixInt16Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
numeric_scaleInt32Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
datetime_precisionInt16Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
character_set_catalogStringReturns 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_schemaStringAlways returns NULL.
character_set_nameStringReturns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
collation_catalogStringReturns 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.

ColumnNameDataTypeDescription
IS_FILESTREAMStringYES if the column has FILESTREAM attribute.

NO if the column does not have FILESTREAM attribute.
IS_SPARSEStringYES if the column is a sparse column.

NO if the column is not a sparse column.
IS_COLUMN_SETStringYES 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.

ColumnNameDataTypeDescription
table_catalogStringCatalog of the table.
table_schemaStringSchema that contains the table.
table_nameStringTable name.
column_nameStringColumn name.
ordinal_positionInt16Column identification number.
column_defaultStringDefault value of the column
is_nullableStringNullability of the column. If this column allows NULL, this column returns YES. Otherwise, NO is returned.
data_typeStringSystem-supplied data type.
character_maximum_lengthInt32Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.
character_octet_lengthInt32Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.
numeric_precisionUnsigned BytePrecision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
numeric_precision_radixInt16Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
numeric_scaleInt32Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
datetime_precisionInt16Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
character_set_catalogStringReturns 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_schemaStringAlways returns NULL.
character_set_nameStringReturns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
collation_catalogStringReturns 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_FILESTREAMStringYES if the column has FILESTREAM attribute.

NO if the column does not have FILESTREAM attribute.
IS_SPARSEStringYES if the column is a sparse column.

NO if the column is not a sparse column.
IS_COLUMN_SETStringYES 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.

ColumnNameDataTypeDescription
table_catalogStringCatalog of the table.
table_schemaStringSchema that contains the table.
table_nameStringTable name.
column_nameStringColumn name.
ordinal_positionInt16Column identification number.
column_defaultStringDefault value of the column
is_nullableStringNullability of the column. If this column allows NULL, this column returns YES. Otherwise, NO is returned.
data_typeStringSystem-supplied data type.
character_maximum_lengthInt32Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.
character_octet_lengthInt32Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.
numeric_precisionUnsigned BytePrecision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
numeric_precision_radixInt16Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
numeric_scaleInt32Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
datetime_precisionInt16Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
character_set_catalogStringReturns 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_schemaStringAlways returns NULL.
character_set_nameStringReturns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
collation_catalogStringReturns 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_FILESTREAMStringYES if the column has FILESTREAM attribute.

NO if the column does not have FILESTREAM attribute.
IS_SPARSEStringYES if the column is a sparse column.

NO if the column is not a sparse column.
IS_COLUMN_SETStringYES if the column is a column set column.

NO if the column is not a column set column.
ColumnNameDataTypeDescription
uidInt16User ID, unique in this database. 1 is the database owner.
nameStringUsername or group name, unique in this database.
createdateDateTimeDate the account was added.
updatedateDateTimeDate the account was last changed.
ColumnNameDataTypeDescription
table_catalogStringCatalog of the view.
table_schemaStringSchema that contains the view.
table_nameStringView name.
check_optionStringType of WITH CHECK OPTION. Is CASCADE if the original view was created using the WITH CHECK OPTION. Otherwise, NONE is returned.
is_updatableStringSpecifies whether the view is updatable. Always returns NO.
ColumnNameDataTypeDescription
view_catalogStringCatalog of the view.
view_schemaStringSchema that contains the view.
view_nameStringView name.
table_catalogStringCatalog of the table that is associated with this view.
table_schemaStringSchema that contains the table that is associated with this view.
table_nameStringName of the table that is associated with the view. Base Table.
column_nameStringColumn name.
ColumnNameDataTypeDescription
assembly_nameStringThe name of the file for the assembly.
UDT_nameStringThe class name for the assembly.
version_majorObjectMajor Version Number.
version_minorObjectMinor Version Number.
version_buildObjectBuild Number.
version_revisionObjectRevision Number.
Culture_infoObjectThe culture information associated with this UDT.
Public_keyObjectThe public key used by this Assembly.
Is_fixed_lengthBooleanSpecifies whether length of type is always same as max_length.
max_lengthInt16Maximum length of type in bytes.
permission_set_descStringThe friendly name for the permission-set/security-level for the assembly.
create_dateDateTimeThe date the assembly was created/registered.

Retrieving Database Schema Information
ADO.NET Managed Providers and DataSet Developer Center

Show: