To read the article online, visit http://www.4GuysFromRolla.com/webtech/090899-1.shtml

Using the sysobjects Table


In SQL Enterprise Manager, when you expand a database you are shown the database's tables, views, stored procedures, and other useful database objects. Have you ever wondered how in the world SQL Server keeps track of the many objects for each database? Not surprisingly, SQL Server uses a table to store the object information for each database. This table, sysobjects, can be queried just like any other table!

Let's say that you wanted to display the tables in your database on a web page. All of the tables in your database are stored in the sysobjects table, so all you need to do is properly query the sysobjects table. Since the sysobjects table contains information on all of your database's objects, we don't want to simple do a SELECT * FROM sysobjects. There is a column in the sysobjects table named Type, which is used to differentiate between each type of database object. Tables are of type U, as in underwear.

So all we need to do is query the sysobjects table for rows that have a U in their Type field. Simple enough.

'Connection object... Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=MyDatabaseName" 'We want to query the sysobjects table where type = 'U' Dim strSQL strSQL = "SELECT * FROM sysobjects WHERE type='U'" 'Execute the query Dim objRS Set objRS = objConn.Execute(strSQL) 'Display the results in a table Response.Write "<TABLE BORDER=1 CELLSPACING=1><TR>" & vbCrLf Dim fld For Each fld in objRS.Fields Response.Write "<TH>" & fld.Name & "</TH>" & vbCrLf Next Response.Write "</TR><TR>" & vbCrLf Do While not objRS.EOF Response.Write "<TR>" & vbCrLf For Each fld in objRS.Fields Response.Write "<TD>" & fld.Value & "</TD>" & vbCrLf Next Response.Write "</TR>" & vbCrLf objRS.MoveNext Loop Response.Write "</TABLE>" 'Clean up... objConn.Close Set objConn = Nothing

There are a lot of columns in the sysobjects table. Some columns you might be most interested in are:

  • Type: This determines what type of database object we are dealing with (table, view, stored procedure, etc.)
  • uid: This is the id of the user that created the object. Once you know the user id with which you created all of your database objects, you can query for records in sysobjects that contain just that user id. That way, you won't see all of the extraneous system objects.
  • name: The actual name of the database object (such as the table name, or the view name)
  • crdate: This is the creation date of the database object
  • id: This unique identifier is important when referencing other system tables

The sysobjects table does not only contain information on the tables in your database. If you wanted to list all of the views, you would simply need to change the SQL query to:

SELECT * FROM sysobjects WHERE type='V'

If you wanted to list the stored procedures for your database, just change the SQL query to:

SELECT * FROM sysobjects WHERE type='P'

The sysobjects table is pretty useful for administrative tasks. Imagine that you want to view all the contents of a given table. You could create a page that listed all of your tables in your database in a form. The user could click on a table and submit the form. The submitted form would take the user to an ASP page that, based on the table name chosen in the form, dumped the contents of the table. This would be a more elegant approach than writing a table-listing script for each table.

Well, that wraps up today's article. I hope you learned something new! Have a great day, and happy programming!


Article Information
Article Title: Using the sysobjects Table
Article Author: Scott Mitchell
Published Date: Wednesday, September 08, 1999
Article URL: http://www.4GuysFromRolla.com/webtech/090899-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers