ASP: how to check SQL Server's tables properties

Tweet

If you use SQL Server as a back end of your web application, sometimes it is important to check a table properties before allowing any delete, update or insert request. Sometimes it is useful just to display the table status to the user, showing - for example - the last modify date, so that the user will be aware of the update level of the data fetched.

To do so, we need to query the sys.Tables of your database. It contains a lot of information on the database tables, the kind of info we are looking for.

Let's start with a simple query. We create - as usual - the connection string, where serverpath is the SQL Server path, dbname is the database name, userid and password are the credential for the db access:

<%
Dim CONN_STRING
CONN_STRING = "Driver={SQL Server};Server=serverpath;Database=dbname;Uid=userid;Pwd=password;"
%>

Now we query the sys.Tables:

<%
Dim Tables
 Set Tables = Server.CreateObject("ADODB.Recordset")
 Tables.ActiveConnection = CONN_STRING
 Tables.Source = "SELECT * FROM sys.Tables"
 Tables.CursorType = 0
 Tables.CursorLocation = 2
 Tables.LockType = 1
 Tables.Open()
%>

In the given example, we query the sys.Tables for every information it stores. For our purposes, it is quite enough to fetch only three columns, and possibly order the resulting recordset by modify date. For that, our query will be:  

SELECT name, create_date, modify_date FROM  sys.Tables ORDER BY modify_date

Now you can display the data in a table:

<table border="1">
    <tr>
      <td><div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Name</strong></font></div></td>
      <td><div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Creation Date</strong></font></div></td>
      <td><div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Modify Date</strong></font></div></td>
    </tr>
<%
While Not Tables.EOF
%>
    <tr>
      <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=(Tables.Fields.Item("name").Value)%></font></td>
      <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=(Tables.Fields.Item("create_date").Value)%></font></td>
      <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=(Tables.Fields.Item("modify_date").Value)%></font></td>
    </tr>
<%
Tables.MoveNext()
Wend
%>
  </table>

And finally we close the recordset:

<%
Tables.Close()
Set Tables = Nothing
%>

This is a simple way of displaying the table information. You can actually use the fetched information for other and more daring purposes. It's up to you.