October 7, 2004 at 12:54 pm
How do i know whats the default values on which table and which column and whats the default value
It should be like this
Table column constraint_name value
emp emp_id DF_emp_emp_no 1
emp join_date DF_emp_cur_date getdate()
How can i get these results on all the tables in my DB.
Thanks.
October 7, 2004 at 1:19 pm
default constraints are in sysobjects type = 'D'
values for these are in syscomments where id = sysobjects id
table object id (sysobjects) is in the parent_obj column of the constraint row in sysobjects
column id (syscolumns) is in the "info" column of the of the constraint row in sysobjects.
Now it's up to you do do the joins...
October 7, 2004 at 2:41 pm
Here you go, try this:
--Get default values for all objects in the database
select object_name(sc.id) AS 'Table Name', scol.name AS 'Column Name',
object_name(sc.constid) AS 'Constraint Name',
scom.text AS 'Default Value' from sysconstraints sc
JOIN syscolumns scol ON sc.colid = scol.colid AND sc.id = scol.id
JOIN syscomments scom ON sc.constid = scom.id
where sc.status & 5 = 5 --bit mask for Default constraint
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply