ADO.NET 3.5 Cookbook, 2nd Edition

7.8. Retrieving Constraints from a SQL Server Database

Problem

You need to programmatically retrieve the constraint information that is defined in a SQL Server database.

Solution

Use the SQL Server Management Objects (SMO), catalog views, or INFORMATION_SCHEMA views to get information about primary key, foreign key, and check constraints. The solution shows how to use all three techniques to retrieve constraint information and output it to the console.

This solution needs a reference to the Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, and Microsoft.SqlServer.SqlEnum assemblies.

The C# code in Program.cs in the project RetrieveConstraintsSqlServer is shown in Example 7-25.

Example 7-25. File: Program.cs for RetrieveConstraintsSqlServer solution

using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Management.Smo; namespace RetrieveConstraintsSqlServer { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; // ---Use SMO--- Console.WriteLine("---SMO---"); Server server = new Server("(local)"); Database db = server.Databases["AdventureWorks"]; Console.WriteLine("---Primary key constraints---"); Console.WriteLine("Row 1:"); bool pkFlag = false; int pkCount = 0; // Iterate over table collection foreach (Table table in db.Tables) { // Iterate over index collection foreach (Index index in table.Indexes) { // Count the primary keys if (index.IndexKeyType ...