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

Now, let us try to develop a simple script (JavaScript) which shows the technique for retrieving all Primary Keys and Foreign Keys available in a SQL Server 2000 instance. Even though the following code gives concatenated information, it can be split according to our requirements. The splitting is easy to accomplish with the combination of regular expressions and arrays in JavaScript. 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_KeyColumn");
      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.GroupComponent + "</td>");
            document.write("<td>" + p.PartComponent + "</td>");
            document.write("</tr>");
      }
      document.write("</table>");
}

functiondispHeading()
{
      document.write("<thead>");
      document.write("<td>Group</td>");
      document.write("<td>Part</td>");
      document.write("</thead>");
}

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

The above would automatically list all the KEY columns available in a SQL Server 2000 instance belonging to the client. To retrieve this information, I used a built-in class, “MSSQL_KeyColumn” available in the “rootMicrosoftSQLServer” namespace. You can also try to use the class “MSSQL_TableKey” to retrieve a similar type of information. Check it out yourself!

Next: How to retrieve only the Primary Keys of all tables in all databases existing in a SQL Server 2000 instance using JavaScript >>