JAVASCRIPT

Retrieving SQL Server 2000 Constraints Information using Client-side JavaScript

This article mainly concentrates on retrieving SQL Server 2000 constraints information using client-side JavaScript. CHECK constraints, Primary and Foreign Keys, and other kinds of constraints are covered.

Author Info:

By: Jagadish Chaterjee

Poor Best

Rating:

/ 4

May 30, 2006

TABLE OF CONTENTS:

· Retrieving SQL Server 2000 Constraints Information using Client-side JavaScript
· How to retrieve all Primary Keys and Foreign Keys existing in a SQL Server 2000 instance using JavaScript
· How to retrieve only the Primary Keys of all tables in all databases existing in a SQL Server 2000 instance using JavaScript
· How to retrieve only the Foreign Keys of all tables in all databases existing in a SQL Server 2000 instance using JavaScript
· How to retrieve all the unique keys existing in all tables from all databases of a SQL Server 2000 instance using JavaScript
· How to retrieve all rules from all databases of a SQL Server 2000 instance using JavaScript

If you are new to working with client-side JavaScript together with SQL Server 2000, I strongly suggest you to go through my article "Combining North Pole and
South Pole: JavaScript with SQL Server 2000.” You will NOT be able to execute any of the examples in this article unless you complete the previous article.

If you are new to working with the types of examples in this article, I suggest you go through my series “Advanced JavaScript with Internet Explorer” at this web site. You can directly copy and paste the complete code samples presented in this article into a file with the extension “.htm” and open them in Internet Explorer 5.5+.

How to retrieve all CHECK constraints existing in a SQL Server 2000 database using JavaScript

Now, let us try to develop a simple script (JavaScript) which shows the technique for retrieving all CHECK constraints available in a SQL Server 2000 database. The entire code for the sample is as follows:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
      <head>
            <title></title>
            <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
            <meta name="vs_targetSchema" content="http://schemas.microsoft.com/
intellisense/ie5">
            <script id="clientEventHandlersJS" language="javascript">
<!--

functionButton1_onclick() {
      var locator = new ActiveXObject ("WbemScripting.SWbemLocator");
      var service = locator.ConnectServer(".","rootMicrosoftSQLServer");
      var properties = service.ExecQuery("SELECT * FROM MSSQL_Check WHERE databasename='Northwind'");
      var e = new Enumerator (properties);
      document.write("<table border=1>");
      dispHeading();
      for (;!e.atEnd();e.moveNext ())
      {
            var p = e.item ();
            document.write("<tr>");
            document.write("<td>" + p.Name + "</td>");
            document.write("<td>" + p.TableName + "</td>");
            document.write("<td>" + p.Checked + "</td>");
            document.write("<td>" + p.Text + "</td>");
            document.write("</tr>");
      }
      document.write("</table>");
}

functiondispHeading()
{
      document.write("<thead>");
      document.write("<td>Name</td>");
      document.write("<td>TableName</td>");
      document.write("<td>Checked</td>");
      document.write("<td>Text</td>");
      document.write("</thead>");
}

//-->
            </script>
      </head>
      <body>         <INPUT id="Button1" type="button" value="Button" name="Button1"
language="javascript" onclick="return Button1_onclick()">
      </body>
</html>

In the above code the “meta” tags are not necessary. They have been automatically added by Visual Studio. The code above would automatically list all the CHECK constraints along with their table names and CHECK conditions available in the SQL Server 2000 database belonging to the client. To retrieve this information, I used a built-in class, “MSSQL_Check” available in the “rootMicrosoftSQLServer” namespace.

The “for” loop I used in the above code iterates for every CHECK constraint present in the SQL Server database and finally retrieves only the properties of that constraint. Keep in mind that I simply provided the database “Northwind.” You need to change it accordingly. You can omit the condition if you need to list from all databases.

Next: How to retrieve all Primary Keys and Foreign Keys existing in a SQL Server 2000 instance using JavaScript >>