Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

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

From Wiki

Jump to: navigation, search

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

  1. CREATE TABLE blah(id INT DEFAULT 0,
  2. SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)
  3.  
  4. INSERT blah DEFAULT VALUES
  5.  
  6. SELECT * FROM blah


SQL 2000/2005

  1. SELECT COLUMN_DEFAULT,*
  2. FROM INFORMATION_SCHEMA.COLUMNS
  3. WHERE TABLE_NAME ='blah'


SQL 2000

  1. SELECT Text,*
  2. FROM sysobjects o
  3. INNER JOIN syscolumns c
  4. ON o.parent_obj = c.id
  5. AND o.info = c.colid
  6. INNER JOIN syscomments s
  7. ON o.id = s.id
  8. WHERE o.xtype = 'D'
  9. AND OBJECT_NAME(parent_obj) = 'blah'


SQL 2005

  1. SELECT * FROM
  2. sys.default_constraints d
  3. JOIN sys.sysobjects o ON d.parent_object_id = o.id
  4. 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.

  1. DROP TABLE blah
  2. CREATE TABLE blah(id INT,SomeDate DATETIME)
  3.  
  4. ALTER TABLE blah
  5. ADD CONSTRAINT DF_Blah_ID_0
  6. DEFAULT 0 FOR id
  7.  
  8. ALTER TABLE blah
  9. ADD CONSTRAINT DF_Blah_SomeDate_Now
  10. DEFAULT CURRENT_TIMESTAMP FOR SomeDate
  11.  
  12.  
  13. INSERT blah DEFAULT VALUES
  14.  
  15. SELECT * FROM blah


SQL 2000/2005

  1. SELECT COLUMN_DEFAULT,*
  2. FROM INFORMATION_SCHEMA.COLUMNS
  3. WHERE TABLE_NAME ='blah'


SQL 2000

  1. SELECT Text,*
  2. FROM sysobjects o
  3. INNER JOIN syscolumns c
  4. ON o.parent_obj = c.id
  5. AND o.info = c.colid
  6. INNER JOIN syscomments s
  7. ON o.id = s.id
  8. WHERE o.xtype = 'D'
  9. AND OBJECT_NAME(parent_obj) = 'blah'


SQL 2005

  1. SELECT * FROM
  2. sys.default_constraints d
  3. JOIN sys.sysobjects o ON d.parent_object_id = o.id
  4. 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

  1. 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

421 Rating: 2.8/5 (45 votes cast)