Published: Wednesday, September 08, 1999
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.
<![CDATA[
'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!
