[ Team LiB ] Recipe 10.2 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.
[ Team LiB ]
