Developing Matt

My Technical Journal

Default value on a column

leave a comment »

What is the default value on my column?  (Take out the criterion for a broader result set)

FROM col.name as col,   defaultvalue = c.text
FROM syscomments c
JOIN
     (SELECT name, cdefault 
      FROM syscolumns
      WHERE id = object_id(‘MYTABLE’)
                  and name = ‘MYCOLUMN’
                  AND cdefault > 0
     )  col on c.id = col.cdefault

 

How do you modify the default value on my column?  I didn’t know, but it is actually a constraint.  So it is a drop and a re-create for a constraint.

ALTER TABLE [dbo].[MYTABLE] drop  CONSTRAINT [MYCONSTRAINT]

ALTER TABLE [dbo].[MYTABLE] ADD  CONSTRAINT [CONSTRAINT]  DEFAULT (‘MYDEFAULTVALUE’) FOR [MYCOLUMN]’

Advertisements

Written by matt

June 26, 2009 at 10:04 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: