How To Find Out Which Columns Have Defaults And What Those Default Values Are
From Wiki
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
Contributed by: --SQLDenis 18:48, 24 June 2008 (GMT)
Part of SQL Server Admin Hacks
