JAVASCRIPT

Digging More into SQL Server 2000 Using Client-side JavaScript

This article mainly concentrates on retrieving SQL Server 2000 information using client-side JavaScript. It is a follow up to an earlier article that focused on retrieving stored procedure information. This article focuses on several other database objects.

Author Info:

By: Jagadish Chaterjee

Poor Best

Rating:

/ 13

May 23, 2006

TABLE OF CONTENTS:

· Digging More into SQL Server 2000 Using Client-side JavaScript
· How to retrieve all the parameter information of a stored procedure existing in an SQL Server 2000 database using JavaScript
· How to retrieve all trigger information (along with source code) existing in an SQL Server 2000 database using JavaScript
· How to retrieve database file information related to a particular SQL Server 2000 database using JavaScript
· How to retrieve all user data concerning a particular SQL Server 2000 database using JavaScript
· How to retrieve the user defined data types (in all databases) existing in a particular SQL Server 2000 instance using JavaScript

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

In my previous article, we stopped at retrieving stored procedure information. In this article, we shall focus on several other database objects.

How to get a list of all user defined functions (along with source code) existing in an SQL Server 2000 database using JavaScript

Now, let us try to develop a simple script (JavaScript) which shows the technique for retrieving all the names of stored functions available in an 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">
<!--

function Button1_onclick() {
      var locator = new ActiveXObject ("WbemScripting.SWbemLocator");
      var service = locator.ConnectServer(".","rootMicrosoftSQLServer");
      var properties = service.ExecQuery("SELECT * FROM MSSQL_UserDefinedFunction 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.Text + "</td>");
            document.write("</tr>");
      }
      document.write("</table>");
}

function dispHeading()
{
      document.write("<thead>");
      document.write("<td>Name</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 above code would automatically list all user-defined function names and source code available in a SQL Server 2000 database belonging to the client. To retrieve this information, I used a built-in class, “MSSQL_UserDefinedFunction” available in the “rootMicrosoftSQLServer” namespace.

The “for” loop I used in the above code iterates for every user-defined function present in the SQL Server database and finally retrieves only the properties of that user-defined function. Please understand that I simply provided the database “Northwind.” You need to change it to a different database which contains user-defined functions.

Next: How to retrieve all the parameter information of a stored procedure existing in an SQL Server 2000 database using JavaScript >>