how do I get the value of a DEFAULT

Hi

We have a default defined in our database

CREATE DEFAULT [ schema_name . ] default_name
AS constant_expression [ ; ]

How can I ge the value of the constant using SQL ?

Also - anyone know why this is going to be removed from a future version of SQL - we use it for partitioning with replicated clients (long story) - but every table has one column which is bound to this default. We find it *very* handy.

Ta
Bruce

take a look at the sys.default_constraints catalog view, the definition column will have the value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||We are recommending that you use default constraints instead of DEFAULTs. Would that not work for you?|||

If we were starting the project now, then yes, but we have many installations around the globe relying on this feature....

Bruce

|||

That shows the default constraints, but not our DEFAULT (terminology so similar but different - I'm confused!)

thanks
bruce

|||

sp_helpconstraint tablename

Adamus

|||

Thats closer, but I really just want to know that the integer value of my DEFAULT is ...

I see it joins on sys.columns and syscomments to get a descriptive string of the default - I was hoping for something a little more direct - in the meantime I'll write a function to work it out..

thanks

|||

syscomments is deprecated in sql server 2005. Please use sys.sql_modules instead or you can use the below built-in object_definition.

select object_definition(default_object_id) from sys.columns where default_object_id <> 0

|||

SELECT d.* FROM sys.default_constraints as d
JOIN sys.objects as o
ON o.object_id = d.parent_object_id
JOIN sys.columns as c
ON c.object_id = o.object_id AND c.column_id = d.parent_column_id
JOIN sys.schemas as s
ON s.schema_id = o.schema_id
WHERE o.name='<TableName>' AND c.name = '<ColumnName>'

The above query will show the default constraint properties of the column specified in the above query.

The default value can be obtained from the column d.[definition] in the above query.

Thanks,
Loonysan

sql