sys.default_constraints empty for temporary tables in tempdb - by siggemannen

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 765777 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 10/2/2012 11:56:30 AM
Access Restriction Public


In SQL Server 2012, If i create a temporary table with a column default, it isn't reflected in sys.default_constraints table anymore.

This is because sys.default_constraints View works with object_id > 0 but all temporary tables have object_id < 0 in SQL Server 2012.

Perhaps a fix of view definition could be:

CREATE VIEW sys.default_constraints AS
	SELECT name, object_id, principal_id, schema_id, parent_object_id,
		type, type_desc, create_date, modify_date,
		is_ms_shipped, is_published, is_schema_published,
		property AS parent_column_id,
		object_definition(object_id) AS definition,
	FROM sys.objects$
	WHERE type = 'D ' AND parent_object_id <> 0
Sign in to post a comment.
Posted by Microsoft on 2/26/2013 at 7:15 PM
Hi, Thank you for your feedback. We have investigated the issue and the fix will be available in the next release of SQL Server. Thanks & Regards, Pooja Harjani - Program Manager - SQL Server