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
