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.

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 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.
index_name String Index 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 Byte The type of object.
index_name String Index Name.

Procedures

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 Int32 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 Int32 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 Int32 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 Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.
CHARACTER_OCTET_LENGTH Int32 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 Int32 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 Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.
CHARACTER_OCTET_LENGTH Int32 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.
user_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

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

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.
Create_Date DateTime The date the assembly was created/registered.
Permission_set_desc String The friendly name for the permission-set/security-level for the assembly.

See also