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

<!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_ForeignKey");
      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.DatabaseName + "</td>");
            document.write("<td>" + p.Description + "</td>");
            document.write("<td>" + p.Checked + "</td>");
            document.write("<td>" + p.ExcludeReplication + "</td>");
            document.write("</tr>");
      }
      document.write("</table>");
}

functiondispHeading()
{
      document.write("<thead>");
      document.write("<td>Name</td>");
      document.write("<td>Table</td>");
      document.write("<td>Database</td>");
      document.write("<td>Description</td>");
      document.write("<td>Checked</td>");
      document.write("<td>Exclude for Replication</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 code would automatically list only the Foreign Key information available in an entire SQL Server 2000 instance belonging to the client. To retrieve this information, I used a built-in class, “MSSQL_ForeignKey” available in the “rootMicrosoftSQLServer” namespace. The “for” loop I used in the above code iterates for every Foreign Key constraint present in the SQL Server database and finally retrieves only the properties of that constraint.

Next: How to retrieve all the unique keys existing in all tables from all databases of a SQL Server 2000 instance using JavaScript >>