How To Find Out Which Columns Have Defaults And What Those Default Values Are

Okay so many many moons ago you created a bunch of tables and those tables have columns of course. You want to know how to find the columns that have defaults.
There are a couple of ways to do this
Below is a list:

1 INFORMATION_SCHEMA.COLUMNS view (2000 and 2005)
2 sysobjects,syscolumns and syscomments (2000 only)
3 sys.default_constraints and sys.sysobjects (2005 only)
4 sp_help (2000 only)


So let's get started with some code
CREATE TABLE blah(id INT DEFAULT 0,
SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)

INSERT blah DEFAULT VALUES

SELECT * FROM blah


--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'

--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'

--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'


See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586C
Instead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.

DROP TABLE blah
CREATE TABLE blah(id INT,SomeDate DATETIME)

ALTER TABLE blah
ADD CONSTRAINT DF_Blah_ID_0
DEFAULT 0 FOR id

ALTER TABLE blah
ADD CONSTRAINT DF_Blah_SomeDate_Now
DEFAULT CURRENT_TIMESTAMP FOR SomeDate


INSERT blah DEFAULT VALUES

SELECT * FROM blah


--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'

--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'

--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'

And last we have sp_help
You can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )
Execute the following
sp_help 'blah'

The defaults will be in the last resultset (the one where the first column name = constraint_type)


And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not