Usually you have to dig through SQL references to find out how to get metadata information for specific DBMS. There are few servers that support same SQL commands. Sometimes the syntax differs slightly, sometimes a server does not support certain statement. Now you can forget about those problems because dotConnect for SQL Server retrieves the metadata for you.
With dotConnect for SQL Server you can take advantage of a very useful feature - GetSchema method. It allows you to read server schema information without writing queries and parsing the output. All information you may want to obtain is brought to you by single function in easy-to-process format. You can get information on databases, tables, columns, indexes, users, stored procedures and functions, user-defined procedures, and reserved words. The method is introduced in System.Data.Common.DbConnection.
To illustrate capabilities of the feature, we have prepared MetaData sample. Please refer to the sample to learn the functionality in a quick glance.
This article consists of the following sections:
GetSchema method is available in three overloads, each of them serves its own purpose. All overloads return System.Data.DataTable object that contains information about server elements.
If you call the GetSchema method without parameters, or with single parameter "MetaDataCollections" (which is actually the same), the table object returned by the method will contain three columns. The first field of every row is a keyword allowed to be passed to the method (as collectionName argument). The second field is the number of restriction values for this keywords (passed through restrictionValues argument). The third field is not used in dotConnect for SQL Server. It is always zero.
GetSchema with 1 argument returns general information about the collection queried. For example, GetSchema("Users") returns list of users on the server.
In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.
The following table provides detailed information on metadata collections that can be
retrieved using the GetSchema method, and restrictions that can be applied for them.
When calling the GetSchema method, you can pass all or few arguments. In the latter case, some
default values are assumed, if they were not specified explicitly. The default value of database
restriction is
Collection Name |
Number of restrictions | Remarks |
---|---|---|
Arguments | 2 |
Returns list of arguments for stored procedures and functions on the server. Restrict it by database name and procedure name. |
Columns | 3 |
Returns list of columns, their type and some extra information.
|
DatasourceInformation | 0 |
Returns information about the data source. |
DataTypes | 0 |
Returns information about data types supported by the data source. |
ForeignKeyColumns | 2 |
Returns list of columns used by foreign keys in the database. Restrict it with database name and table name. |
ForeignKeys | 3 |
Returns list of columns that participate in foreign keys.
|
Functions | 2 |
Returns list of stored functions on the server. Restrict it by database name and function name. |
IndexColumns | 4 |
Returns list of indexed columns in the database, their type and some extra information. Restrict it with database name, table name, index name pattern, and column name pattern. |
Indexes | 3 |
Returns list of indexes and their details.
|
MetaDataCollections | 0 |
Returns this list. Same as using GetSchema() method without parameters. |
PrimaryKeys | 2 |
Returns list of columns that participate in primary keys.
|
Procedures | 2 |
Returns list of stored procedures on the server. Restrict it by database name and procedure name. |
ReservedWords | 0 |
Lists all reserved words used in the server. |
Restrictions | 0 |
Returns list of possible restrictions and their default values for the metadata collections. |
Tables | 2 |
GetSchema("Tables") returns list of tables in the current database.
|
UniqueKeys | 2 |
Returns list of columns that participate in unique keys.
|
UserPrivileges | 1 |
Lists all users and their privileges on the server. When restricted by username, returns information about specific user. |
Users | 1 |
Lists all users on the server. When restricted by username, returns information about specific user. |
ViewColumns | 3 |
Returns list of columns used by views in the database. Restrict it with database name, table name and column name. |
Views | 2 |
GetSchema("Views") returns list of views in the current database.
|
The following code fragment is an elegant way to detect the existence of a table in the Test database.
The next sample shows how to retrieve columns information from a table and render it to console.
The following sample demonstrates how to generate SQL CREATE TABLE statement basing on metadata retrieved with GetSchema method. The generated script will work with all database management systems that support ANSI standard. Only column name and type are included in the script.
Also you can get a metadata of query result set using the GetSchemaTable method of SqlDataReader and the ShemaTable property of SqlDataTable classes.
SqlConnection Class | DbConnectionBase.GetSchema Method | dotConnect for SQL Server Articles