March Madness – SQL Server System Tables – sysobjects

Today we will be talking about the sysobjects system table. This system table is scoped at the database level (not the instance level), so take note of that when you are building scripts. You always need to be mindful about the scope of the system tables otherwise it will lead to some unexpected results.

As with all the other disclaimers, please note that this system table will be removed from a future version of SQL Server.

What is this table for?

This table will contain one row for each and every object that is created in a database. What is an object? Great question! I think of objects as logical containers, such as a table or view, or code such as a stored procedure or function. Those four items (table, view, stored procedure, function) are the most common objects you will see inside a database, with one exception: indexes. Apparently indexes are so special they get their very own system table (patience, Grasshopper, we will talk about them in a later post).

(It is also apparent that some shops and vendors have no idea that indexes exist, but that is a blog rant for another day).

Why should you care?

Because sometimes you need to know these things. That’s why. Not a good enough reason? OK then, let’s look at some specific examples. Let’s say you want a list of all stored procedures in a database:

SELECT *
FROM sysobjects
WHERE type = 'P'

Or, maybe you want a list of all user tables:

SELECT *
FROM sysobjects
WHERE type = 'U'

Or maybe you want to search for a particular table in order to perform a CREATE should the table not already exist. You can do this in a few different ways, one of which is as follows:

IF NOT EXISTS(SELECT name FROM Adventureworks..sysobjects WHERE name = N'TableName' AND xtype='U')
 CREATE TABLE TableName (MyColumn varchar(10))

Where else is this information?

Remember how I said that this system table will be removed from a future version of SQL Server? Yeah, so instead you want to be using the sys.objects catalog view, which makes it really easy to do things like see which objects have been modified in the past week:

SELECT name AS object_name, SCHEMA_NAME(schema_id) AS schema_name,
    type_desc, create_date, modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 7
ORDER BY modify_date;
GO

Enjoy!