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
Type field. Simple enough.
There are a lot of columns in the
sysobjects table. Some columns you might be most interested
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
sysobjectsthat 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:
If you wanted to list the stored procedures for your database, just change the SQL query to:
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!