Retrieving Database Schema Information from SQL Server

Problem

You need to retrieve database schema information from a SQL Server database.

Solution

Retrieve table schema information using either information schema views or the OLE DB .NET data provider Connection object.

The sample code retrieves a list of tables in the Northwind sample database.

The C# code is shown in Example 10-2.

Example 10-2. File: DatabaseSchemaForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

// . . . 

DataTable schemaTable;

if(sqlServerRadioButton.Checked)
{
 String getSchemaTableText = "SELECT * " +
 "FROM INFORMATION_SCHEMA.TABLES " +
 "WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_TYPE";

 // Retrieve the schema table contents.
 SqlDataAdapter da = new SqlDataAdapter(getSchemaTableText,
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 schemaTable = new DataTable( );
 da.Fill(schemaTable);

 schemaDataGrid.CaptionText = "SQL Server .NET Provider";
}
else
{
 OleDbConnection conn = new OleDbConnection(
 ConfigurationSettings.AppSettings["OleDb_ConnectString"]);
 conn.Open( );
 // Get the schema table.
 schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
 new object[] {null, null, null, "TABLE"});
 conn.Close( );

 schemaDataGrid.CaptionText = "OLE DB .NET Provider";
}

// Bind the default view of schema table to the grid.
schemaDataGrid.DataSource = schemaTable.DefaultView;

Discussion

The first solution uses information schema views that are available in SQL Server 7.0 and later. These views provide system-table independent access to SQL Server metadata. Although based on the sysobjects and syscomments system tables, the views allow applications to continue to work properly even if the system tables change. They provide an alternative to the system stored procedures that were previously used and are still available. The INFORMATION_SCHEMA views conform to the SQL-92 Standard.

The views are defined within each database in a schema named INFORMATION_SCHEMA . To access them, specify the fully qualified view name . In the solution, the view for the tables is accessed through the following syntax:

INFORMATION_SCHEMA.TABLES

Table 10-1 lists the information schema views available in SQL Server 2000.

Table 10-1. Information schema views

Name	Description
CHECK CONSTRAINTS	CHECK constraints
COLUMN_DOMAIN_USAGE	Columns that have a user -defined data type
COLUMN_PRIVILEGES	Columns with a privilege granted to or by the current user
COLUMNS	All columns
CONSTRAINT_COLUMN_USAGE	Columns that have a constraint defined on them
CONSTRAINT_TABLE_USAGE	Tables that have a constraint defined on them
DOMAIN_CONSTRAINTS	User-defined data types with a rule bound to them
DOMAINS	All user-defined data types
KEY_COLUMN_USAGE	Columns constrained as a key
PARAMETERS	All parameters for user-defined functions and stored procedures
REFERENTIAL_CONSTRAINTS	All foreign constraints
ROUTINE_COLUMNS	Columns returned by table-valued functions
ROUTINES	All user-defined functions and stored procedures
SCHEMATA	All databases
TABLE_CONSTRAINTS	All table constraints
TABLE_PRIVILEGES	Tables with a privilege granted to or by the current user
TABLES	All tables
VIEW_COLUMN_USAGE	Columns used in a view definition
VIEW_TABLE_USAGE	Tables used in a view
VIEWS	All views

The metadata returned will be limited to that which the user has permission to view. Like any other views, information schema views can also be joined in queries or participate in complex queries to extract specific information. For detailed information about the different views available, refer to SQL Server Books Online.

The solution shows how to retrieve table metadata using the INFORMATION_SCHEMA.TABLES view. It returns data as shown in Table 10-2.

Table 10-2. INFORMATION_SCHEMA.TABLES metadata

Column name	Data type	Description
TABLE_CATALOG	nvarchar (128)	Database name
TABLE_SCHEMA	nvarchar (128)	Table owner
TABLE_NAME	sysname	Table name
TABLE_TYPE	varchar (10)	Table type (either BASE_TABLE or VIEW )

The TABLES view is queried for all columns where the table type is BASE_TABLE in order to return only information about tables and not views.

The second solution uses the GetOleDbSchemaTable( ) method of the OleDbConnection object. This method returns schema information from a database as indicated by a GUID enumerated in the OleDbSchemaGuid class and detailed in Table 10-3.

Table 10-3. OleDbSchemaGuid public fields

Field	Description
Assertions	Assertions
Catalogs	Physical attributes and assertions for catalogs accessible from the data source
Character_Sets	Character sets
Check_Constraints	Check constraints
Check_Constraints_By_Table	Check constraints defined for a catalog
Collations	Character collations
Columns	Columns in tables and view
Column_Domain_Usage	Columns that are dependant on a domain defined in the catalog
Column_Privileges	Privileges on columns
Constraint_Column_Usage	Columns used by referential constraints, unique constraints, check constraints, and assertions
Constraint_Table_Usage	Tables used by referential constraints, unique constraints, check constraints, and assertions
DbInfoLiterals	Provider-specific literals used in text commands
Foreign_Keys	Foreign key columns
Indexes	Indexes
Key_Column_Usage	Columns constrained as keys
Primary_Keys	Columns that comprise primary keys
Procedures	Procedures
Procedure_Columns	Columns of row sets returned by procedures
Procedure_Parameters	Parameters and return codes of procedures
Provider_Types	Base data types supported by the .NET data provider for OLE DB
Referential_Constraints	Referential constraints
Schemata	Schema objects
Sql_Languages	Conformance levels, options, and dialects supported by the SQL implementation processing data
Statistics	Statistics
Tables	Tables and views
Tables_Info	Tables and views
Table_Constraints	Table constraints
Table_Privileges	Table privileges
Table_Statistics	Available statistics on tables
Translations	Defined character translations
Trustee	Trustee defined in the data source
Usage_Privileges	USAGE privileges on objects
Views	Views
View_Column_Usage	Columns in views
View_Table_Usage	Tables in views

As for information schema views, the metadata returned is limited to that which the user has permission to view. In addition to taking the Guid schema argument, you can further restrict the results of the GetOleDbSchemaTable( ) through the second argument, which is an object array specifying column restrictions applied to the result columns in the order in which they are returned. In this example, the schema argument is Tables , which returns a four-column result set containing all tables and views in the database. The fourth column describes the table type; specifying TABLE as the fourth object in the restrictions object array limits the result set to user tables.