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

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

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

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.