Retrieving Constraints from a SQL Server Database

[ Team LiB ] Recipe 6.9 Retrieving Constraints from a SQL Server Database Problem You need to programmatically define constraints in a DataSet and retrieve constraint information defined in a SQL Server database. Solution Use the INFORMATION_SCHEMA views and SQL Server system tables to get information about primary keys, foreign keys, and check constraints.
Nội dung trích xuất từ tài liệu:
Retrieving Constraints from a SQL Server Database[ Team LiB ]Recipe 6.9 Retrieving Constraints from a SQL Server DatabaseProblemYou need to programmatically define constraints in a DataSet and retrieve constraintinformation defined in a SQL Server database.SolutionUse the INFORMATION_SCHEMA views and SQL Server system tables to getinformation about primary keys, foreign keys, and check constraints.The sample code contains one event handler:Get Constraints Button.Click Uses a SQL select statement to load the specified constraint information—primary key, foreign key, or check constraint—from the INFORMATION_SCHEMA views into a DataTable.The C# code is shown in Example 6-27.Example 6-27. File: ConstraintForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Data.OleDb;private const String GETPRIMARYKEYCONSTRAINTS = SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, + kcu.COLUMN_NAME, kcu.ORDINAL_POSITION + FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc + JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON + tc.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME + WHERE tc.CONSTRAINT_TYPE=PRIMARY KEY + ORDER BY tc.TABLE_NAME, kcu.COLUMN_NAME,kcu.ORDINAL_POSITION;private const String GETFOREIGNKEYCONSTRAINTS = SELECT rc.CONSTRAINT_NAME, rc.UPDATE_RULE, rc.DELETE_RULE, + kcuP.TABLE_NAME ParentTable, kcuC.TABLE_NAME ChildTable, + kcuP.COLUMN_NAME ParentColumn, kcuC.COLUMN_NAME ChildColumn + FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc + LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON + rc.UNIQUE_CONSTRAINT_NAME=kcuP.CONSTRAINT_NAME + LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON + rc.CONSTRAINT_NAME=kcuC.CONSTRAINT_NAME AND + kcuP.ORDINAL_POSITION=kcuC.ORDINAL_POSITION + ORDER BY kcuP.TABLE_NAME, kcuC.TABLE_NAME,kcuP.ORDINAL_POSITION;private const String GETCHECKCONSTRAINTS = SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME, cc.CHECK_CLAUSE + FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc + JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON + tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME + WHERE CONSTRAINT_TYPE=CHECK + ORDER BY tc.TABLE_NAME, cc.CONSTRAINT_NAME;// . . .private void getConstraintsButton_Click(object sender, System.EventArgs e){ // Create the DataAdapter to retrieve schema information. SqlDataAdapter da = null; if (primaryKeyRadioButton.Checked) da = new SqlDataAdapter(GETPRIMARYKEYCONSTRAINTS, ConfigurationSettings.AppSettings[Sql_ConnectString]); else if (foreignKeyRadioButton.Checked) da = new SqlDataAdapter(GETFOREIGNKEYCONSTRAINTS, ConfigurationSettings.AppSettings[Sql_ConnectString]); else if (checkRadioButton.Checked) da = new SqlDataAdapter(GETCHECKCONSTRAINTS, ConfigurationSettings.AppSettings[Sql_ConnectString]); // Create and fill table with schema information. DataTable dt = new DataTable( ); da.Fill(dt); // Bind the default view of the table with the grid. constraintsDataGrid.DataSource = dt.DefaultView;}DiscussionInformation schema views were first available in SQL Server 7.0 and later. They providesystem-table independent access to SQL Server metadata. The views are based on systemtables and provide a layer of abstraction that allows applications to continue to workproperly if the system tables change in future releases of SQL Server. Informationschema views provide an alternative to using system stored procedures that werepreviously and are still available. The INFORMATION_SCHEMA views conform to theSQL-92 Standard.Information schema views are defined within each database in a schema namedINFORMATION_SCHEMA. To access the views, specify the fully qualified view name.In the solution, for example, the view containing metadata about the tables in thedatabase is accessed using the following syntax:INFORMATION_SCHEMA.TABLESThe metadata returned is limited to that which the user has permission to view. Like anyother views, information schema views can also be joined in queries or participate incomplex queries to extract specific information. For detailed information about thedifferent views available, refer to SQL Server Books Online.The following three subsections explain how the solution retrieves details about theprimary key, the foreign key, and the check constraints in the database. The informationschema views that are used in the solution are described in the subsection following thosesubsections.Primary key constraintsPrimary key information is obtained by querying the TABLE_CONSTRAINTS (Table 6-11) and KEY_COLUMN_USAGE (Table 6-9) information schema views. The views arejoined on the CONSTRAINT_NAME field and restricted to constraints with aCONSTRAINT_TYPE of Primary Key. The result set is sorted on the TABLE_NAME,COLUMN_NAME, and ORDINAL_POSITION fields.SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITIONFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAMEWHERE tc.CONSTRAINT_TYPE = PRIMARY KEYORDER BY tc.TABLE_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITIONForeign key constraintsForeign key information is obtained by querying the REFERENTIAL_CONSTRAINTS(Table 6-10) and KEY_COLUMN_USAGE (Table 6-9) information schema views. TheREFERENTIAL_CONSTRAINTS view is joined to the KEY_COLUMN_USAGE viewon the UNIQUE_CONSTRAINT_NAME column to return information about the parenttable and its columns. The REFERENTIAL_CONSTRAINTS view is joined again to theKEY_COLUMN_USAGE view on the CONSTRAINT_NAME matching theORDINAL_POSITION of the parent column to return information about the child tableand its columns. The result set is sorted in ascending order on the parentTABLE_NAME, child TABL ...