Return to Answer

This query finds default constraints for a given table. It aint pretty, I agree:

select col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id(N'dbo.test') and dobj.name is not null 

[EDIT] Updated per Julien N's comment

This query finds default constraints for a given table. It ain't pretty, I agree:

select col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id(N'dbo.test') and dobj.name is not null 

This query finds default constraints for a given table. It aint pretty, I agree:

select col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id(N'dbo.test') and dobj.name is not null 

This query finds default constraints for a given table. It aint pretty, I agree:

select col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id(N'dbo.test') and dobj.name is not null 

[EDIT] Updated per Julien N's comment

Aloha

This query finds default constraints for a given table. It aint pretty, I agree:

select col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id(N'dbo.test') and dobj.name is not null 

-Edoode

This query finds default constraints for a given table. It aint pretty, I agree:

select col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name from sys.columns col left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' where col.object_id = object_id(N'dbo.test') and dobj.name is not null