The Power of SysObjects - The Inner Workings of Your Database Revealed!

Hello and welcome to SQL Coach!!

Tonight I want to share some information with you that I find extremely useful in my daily work.

As you may already know... inside the Tables section (in your Object Explorer window) of your database SQL Server contains what are called System Tables.

SQL Server uses these System Tables to store information describing your database objects and statistics about the use of your objects.

I feel the need to stop right here and say DO NOT INSERT, UPDATE or DELETE any data in the System Tables. Like I said, these are for SQL Server to maintain, not you (or me).

However, there is no problem in SELECTing the data contained in these tables. =)

The tables I want you to learn about are called SysObjects and SysComments.

There are many columns in each table and they can be intimidating for a beginner. So, let's do something easy to show you their "bark is worse than their bite."

First a super quick summary of these tables is in order.

SysObjects stores basic information about all objects inside your database. It's useful for you to know because it tells us the name of each object and the type of the object.

SysComments stores the actual text (code) for your stored procedures and functions. It contains an ID field that maps back to the id field in SysObjects.

Now, let's do some quick queries to help you find your way around these two.

SELECT name
FROM dbo.sysobjects

Executing the statement above will list all objects associated with your database. Probably not that useful but maybe kind of interesting if you want an overall feel for the size of database you are working in.

Now, we'll make use of the xtype field found inside SysObjects:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U'

Executing the statement above will list all User tables inside your database.

Let's have a look at the Procedures inside your database:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'P'

Executing the statement above will list all Procedures inside your database (if you want to look at your functions use 'FN' for scalar functions and 'TF' for table functions).

Now, I bet you have a good idea how the Object Explorer window knows what to show, huh?

That's it for now... join us later this week and we'll take a good look at the SysComments table.