Many people are now using the new SQL syntax to embed defaults, check constraints, and foreign key constraints directly in their tables.  This is now even easier through the table definition dialog that Enterprise Manager gives us.  This has one disadvantage though, not being able to get at them.  Picture this:  You walk into a new client/company and need to do some work on their databases.  You begin by getting a familiarity with what is there.  Then your first shock comes when you see a list of over 700 tables in the database.  You work with some of the information to begin to get a handle on things.  Eventually, you come to the defaults.  You go to the Defaults item in Enterprise Manager and see only a handful of defaults.  You know there are more, so you query sysobjects and up pops more than 250 defaults.  So, what do you do?  Most people would get out the trusty word processor or pad of paper and begin going through the table definitions and writing all of them down.  One other problem pops up here.  What default name goes with what column?  This is almost impossible to find inside Enterprise Manager.  This brings to mind one of the best quotes I have ever heard that came from a 12th grade Math teacher.  "The best mathematician is a lazy mathematician, not lazy enough that he doesn't do his work, but lazy enough to find the easy way."  Being lazy, I would definitely like my computer to do most or all of the work.  After all, that's what they made them for!  So the $10 question is, how do you possibly track these down and get a coherent list of the defaults in the database with their definitions?

The answer is from those very same system tables.  But, this one isn't that straight forward.  Below is a very simply query to generate this list.   The query is simple once you understand what is going on within the system tables and how the pieces of data are stored.

select d.name as TableName, c.name as ColumnName, a.name as DefaultName, e.text as DefaultValue
from sysobjects a, sysconstraints b, syscolumns c, sysobjects d, syscomments e
where a.id = b.constid and
b.colid = c.colid and
b.id = c.id and
c.id = d.id and
a.id = e.id and
a.type = 'D' and
a.category = 2048 order by a.name

As you have noticed, sysobjects appears twice in this script.  The first time, it retrieves the name of the default.  The second time, it gives the name of the table after winding its way through the sysconstraints and syscolumns tables.  Finally, the definition of the default is retrieved through syscomments.  There are two things to note in this script.  A type of D in sysobjects designates a default.  This gets both the defaults that were defined as objects and the defaults that were created within the create table statement.  The defaults that exist as physically created objects have a category value of 0.  The defaults that were created as part of a create table statement have a category value of 2048.  You need to use the other tables, because the only value you start with is the object ID of teh default.  This is joined with sysconstraints to get a corresponding column ID and table ID.  These are used to retrieve the column name from syscolumns and the table name from sysobjects.   Finally, the object ID corresponding to the default is joined with the syscomments table to get the definition of the default.

This showed one other thing most people are not aware of.  When you create that default within your table definition, SQL Server still creates an object for that default.   (The same is true for a primary key.  But, I haven't checked foreign key constraints or check constraints.)  The problem is that an object is created every time you define one and they can not be reused.  Say you are tracking data for entry and modification of rows as well as the person who initially created the row or modified it, just like most of us do.  You then have at least 4 of these columns in each table in your database.   Take the 700+ table database.  This equates to over 2800 objects just to support this very simple requirement.  If you utilize a separate default object instead, you have exactly 2 objects.  This means you have saved creating over 2800 objects by doing things a different way.  Now to extend this, you could bind this default to each of the 2800+ columns.  Who wants to do that?  Instead create a user defined datatype for these types of columns and use that datatype instead of the system datatype.  You can now bind the default to the datatype which results in 2 operations instead of 2800+.

Now, you might say that the default created in the table definition is faster and more efficient than the separate object.  That's a possibility, but I'd like someone to give me some hard numbers on the difference between the two.  No overhead.   Create a table that has as many columns as you want.  Add a default as a physical part of the table and insert one row.  Compare this to the time it takes to insert one row with a default bound to that column instead.  Try it with 1000 rows.   Due to the miniscule amount of time, you might start to see a difference in the 10000 row range and up.  But, it will be tiny.  Since none of us creates a database that looks like this, it's pretty much a moot point in real life.  The bottleneck is going to be that trigger on the table well before it'll be the default.   Balancing this type on performance gain against a database that is much easier to adminster is not much of a comparison.